See the image for the table structure I have created:
The hierarchy is like this (from top to bottom):
- dumped_order (dumped_order_id is auto-increment)
- dumped_order_carrier (dumped_order can have many dumped_order_carriers)
- 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.