#1
  1. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375

    Database design question - duplicate data or not?


    say you had a complex database, so to get some info you needed to link few tables together. Would it be better then to repeat some of the info(Ids, which never tend to change) on different tables? so that querying then becomes easier and quicker? or should we always stick with normalised database..
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    this is extremely vague, so the best answer i can give is "it depends"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    sorry,

    ok a non-real example, in my db, on some occasions i have to join four/five tables to get the data i need

    table1: user_id,
    table2: order_id, user_id
    table3: order_it, product_id, user_id,..

    now if i add a user_id in table3, then i wont have to do a join query and get the info i need and query time will be less especially since I have a web app and the lower the page execution time, the better for me?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by paulh1983
    ok a non-real example
    excellent

    so here's a non-real answer --

    sure, it will be better for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by paulh1983
    table1: user_id,
    table2: order_id, user_id
    table3: order_it, product_id, user_id,..

    now if i add a user_id in table3, then i wont have to do a join query and get the info i need and query time will be less especially since I have a web app and the lower the page execution time, the better for me?
    And how do you ensure that the user_id in table3 is always the same as the one in table2?

    Think about the following:

    table1 values: (1),(2)
    table2 values: (1,1)
    table3 values: (1,1,2)

    If you use a join between table2 and table3 you always get the correct user_id. The only way to achieve this would be to make (order_id, user_id) the primary key in table2 and make (order_id, user_id) a foreign key referencing table2. Whether or not you can actually make (order_id, user_id) the PK (i.e. unique) I abviously don't know
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    Originally Posted by r937
    excellent

    so here's a non-real answer --

    sure, it will be better for you
    well of course it is a real example because people use such a model for ORDER/shopping site.. but its not real because "i am not using it" but the example perfectly illustrate what I am trying to achieve
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    Originally Posted by shammat
    And how do you ensure that the user_id in table3 is always the same as the one in table2?

    Think about the following:

    table1 values: (1),(2)
    table2 values: (1,1)
    table3 values: (1,1,2)

    If you use a join between table2 and table3 you always get the correct user_id. The only way to achieve this would be to make (order_id, user_id) the primary key in table2 and make (order_id, user_id) a foreign key referencing table2. Whether or not you can actually make (order_id, user_id) the PK (i.e. unique) I abviously don't know
    cheers, I know it is a bit risky to deviate from the normalised database by adding DUPLICATE data just to "ease" the query building. but in my mind I was thinking if I can duplicate data, that will result in faster query etc etc.

    I'll stick with normalised DB and do the query the "hard" way. Thanks

IMN logo majestic logo threadwatch logo seochat tools logo