I need to show packaging - eg glass jar and cardboard box. I need to be able to show sizes and weights for postage purposes.
I am stuck on what to use for the PK on the second table. what would you recommend. surrogate key set to auto_increment? I was hoping to use a natural key but in trying to find one, I wonder if I should have a one-to-many relationship between container & serial_number AND each possible size.
As I had ‘planned’ it, there would not be confusion between the several columns and the unit of weight.
The measurements are attributes of the packet size. but across the range they can be sizes of vloume, height x width, cubic or length. whatever type, the unit would be consistent per record since we wouldn’t be having 24cm x 3 inches x 5 feet.
Your hint seems to steer me towards this place
create table package sizes
( id int not null auto_increment primary key
, container_name
, container size
, unit of measurement
) ..
smaple data
| 1 | glass jar A | 50 | ml |
| 2 | packet | 24 x 12 x 6 | cm |
But what if I want to search through package sizes, to determine which is most suitable for the postage costs?
you’re asking questions that really belong in the realm of the subject matter expert, where the subject matter in this case is not database design but user requirements
i don’t know what you need in your tables, because i don’t know what you want to record