SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Db design: should I be concerned about PK spanning many columns?

    See the image for the table structure I have created:


    The hierarchy is like this (from top to bottom):

    1. dumped_order (dumped_order_id is auto-increment)
    2. dumped_order_carrier (dumped_order can have many dumped_order_carriers)
    3. dumped_order_carrier_option (dumped_order_carrier can have many dumped_order_carrier_options)

    dumped_order_carrier doesn't have an auto-increment id, it has a primary key spanning 3 columns - I have made it so because the 3-column value sets will always be unique. Next I wanted to add another table which would hold multiple options assigned to dumped_order_carrier and so in order to reference the parent table I have to again use the 3-column PK.

    I wonder if it would be a better idea to add an auto-increment id to dumped_order_carrier and change the current 3-column PK to a unique key. This way I wouldn't have to repeat the 3-column PK in dumped_order_carrier_option - I could simply reference the parent table (dumped_order_carrier) by its auto-increment id.

    I know both approaches would work, I'm interested in what would be the more logical/elegant practice. The current setup looks logical to me but I'm concerned a bit about repeating all the columns.
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    more logical and elegant -- use multi-column PKs/FKs

    if you used an auto_increment, you would still, like you said, have to repeat the multi columns anyway, ans well as adding an additional index to ensure uniqueness, which means the auto_increment would be pretty much useless (e.g. would you ever search a child table by its auto_increment value? nope)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    more logical and elegant -- use multi-column PKs/FKs

    if you used an auto_increment, you would still, like you said, have to repeat the multi columns anyway, ans well as adding an additional index to ensure uniqueness, which means the auto_increment would be pretty much useless
    Well, the auto_increment would be useless in the parent table but it would be useful in the child table because I could reference the parent table by 1 column instead of 3. I was also thinking about scaling - if, in the future I add other child tables to dumped_order_carrier or dumped_order_carrier_option then I will have to repeat all the 3 columns in each one. If I need to extend the PK to span 4 or more columns instead of 3, then I'll have to update all foreign keys in the child tables. Possibly, I'd also have to update a few queries and other logic in my application. With the additional auto-increment all that work wouldn't be necessary.
    (e.g. would you ever search a child table by its auto_increment value? nope)
    No but I will search the child table by its foreign key value. In this case the FK spans 3 columns so I have to search by all of them. With the additional auto-increment I could have a single column FK, which would be easier to manage.

    I agree with you that it's more logical and elegant to use multi-column keys. It only seems more work in case of any future changes. I'll leave it now as it is and see how it works out.


Bookmarks

Posting Permissions

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