*
The moose likes JDBC and the fly likes database designing issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "database designing issue" Watch "database designing issue" New topic
Author

database designing issue

vikas sharmaa
Ranch Hand

Joined: Jun 28, 2007
Posts: 191
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

Joined: Oct 13, 2005
Posts: 38007
    
  22
Do the tables represent features of the same thing?
vikas sharmaa
Ranch Hand

Joined: Jun 28, 2007
Posts: 191
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

Joined: Oct 13, 2005
Posts: 38007
    
  22
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

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.


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: database designing issue
     
    Similar Threads
    Splitting Values
    Updation of child table.
    Mapping problem
    resolving many to many relatiionship
    Help with a query?