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

[Lemon Juice]

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.

[r937]

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)

[Lemon Juice]

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.