locked
Design Advantages of One to One Relationship Over Single Table design RRS feed

  • Question


  • I had a discussion with my team mate over designing [AccountTransactionInfo] And [AccountTransaction].

    a) I say if we have these data separated into two tables it will be more consistent because we may not have any data for columns in [AccountTransactionInfo] so will not have any rows for non- existent data , and if we mix both columns in one table we have to have null values for non existent data.

    on the other hand

    b) my colleague says if we mix both tables into one table it would be better because we can have all columns in one table instead of two table , having advantage of less insert commands versus having one extra update command if we want to keep the columns separated into 2 tables.

     

    I would appreciate your opinion on this design issue  we have ,

    Thank you very much for your cooperation.


    Sunday, November 6, 2011 12:14 PM

Answers

  • Hmm, Amount,Comment,IsApproved,ApprovedDate,(BTW why do you have two Comment columns?) depends on AccountTransactionID...Based on the narrative(I do not know your business requirements) it looks like it can one table .So some of the fields at some stage will NULL, I do not think it could be a problem
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 6, 2011 12:37 PM

All replies

  • Hmm, Amount,Comment,IsApproved,ApprovedDate,(BTW why do you have two Comment columns?) depends on AccountTransactionID...Based on the narrative(I do not know your business requirements) it looks like it can one table .So some of the fields at some stage will NULL, I do not think it could be a problem
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 6, 2011 12:37 PM
  • I don't see an advantage of two tables for a one-to-one relationship like this. Having some NULL values in those columns seems less significant that having to maintain two tables and perform the join each time you query the table. Seems easier to just have all columns in a single table.
    Sunday, November 6, 2011 3:36 PM
  • For this particular type of 1 to 1 relationship with only a few optional attributes there is no advantage of having 2 separate tables, so 1 table should be efficient.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, November 6, 2011 4:21 PM
    Answerer
  • If there's no functional reason to have 2 TABLEs, it should be 1 TABLE.

    As for the NULL "values", technically, NULL is not a value, and it takes up very little storage.

    Monday, November 7, 2011 1:41 PM
    Answerer