Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Chicago, IL

    Unanswered: Database Design Question

    I have a table that has a composite primary key. It is made up of four fields. I am creating a second table that will track additional information about the primary key. The relationship between the two tables is a one-to-many. My question is this, is it better to create a separate field in the one table (AutoNumber) and use this in the many table. Or, do I duplicate the structure in the many table each time I add a record. I am interested to know the pros and cons of each approach.

    I personally am leaning toward a second field (Autonumber) so I don't have to add four fields each time I add a field. But somehow I feel like this might be poor design (although I can't find fault with it right now).

  2. #2
    Join Date
    May 2005
    It is always good database design to NOT duplicate information from table to table. That's the beauty of a relational database! Autonumber is the best way to go.

    Unless, of course, you have a very powerful server that you'd like to see doing more work than necessary...

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Hi DC

    I spotted this and I know it's an oldie but... couldn't resist

    Surrogate Keys Vs Natural Keys is a long standing debate with fanatics on both sides. I don't think it is cut and dried either way and there are heavy weights on both sides of the fence for this one. There are those that even argue that surrogate keys violate normal form (someone on this baord pointed that out and I didn't realise quite what the implications were at first).

    Relational design is about not duplicating data from table to table.... apart from primary and foreign keys where it is a necessity. Surrogate keys are a purely physical implementation - they shouldn't really ever appear in a logical design. As far as performance is concerned... yes - a single integer primary to foreign key link is more efficient than a composite of text keys. However, my experience is you need to include many more tables in your queries (especially management summary "how many" and "what cost" type queries) because there is no longer any information in the join.

    Anyway - enough waffle. Here are a few of the links I have come up with whilst investigating the topic. Please let me know if you stumble on anything else that is interesting:

    Oh and one last thing - a surogate key does not enforce uniqueness! Remember to uniquely constrain your natural keys too!
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts