Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database designing issue

 
vikas sharmaa
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if 2 tables have one-to-one relationship then we should always merge the columns of these 2 table and create 1 table instead. is my understanding correct?
 
Campbell Ritchie
Sheriff
Pie
Posts: 49786
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do the tables represent features of the same thing?
 
vikas sharmaa
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes the features of same thing. actually i am parsing an XML file and then transferring its data into the database.

Let me elaborate my question by giving example:

suppose my xml file is the below one:



i want to design a database to store above xml file information. one table that i decided is Question table that contains que_number, question, and answer columns. now, whether i should create another table for score element or shall i add max_score, min_score, and actual_score columns to the Question table itself. what shall be the better approach. please guide me. thank you.
 
Campbell Ritchie
Sheriff
Pie
Posts: 49786
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I hope somebody else will comment, because I might be mistaken, but how can a score be bound to a question? Surely it is bound to whoever answered the question as well. In which case it would fit better into a separate table. You have a question table with an answer, then you have a participant table, and you link answer given with the question, so you can see whether the answer was correct or incorrect.

I am sure other people will have different suggestions, so fire away . . .
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
vikas sharmaa wrote:if 2 tables have one-to-one relationship then we should always merge the columns of these 2 table and create 1 table instead. is my understanding correct?

Very little is always true.

I can think of two reasons for creating a second table:
  • independent entity - if a score made sense on it's own, this might make sense
  • large data - suppose you were storing the question text (and it's more than a sentence.) Having the question text in a separate table could speed up queries on the main table.


  • In your example, I think min and max score could go with the question. The actual score seems like it goes with a user/instance of the exam.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic