New Member
Posts: 1
Registered: ‎09-15-2021

Best practices when relating multiple tables

So I'm the sole programmer at a small production company, where I provide logistics backed by a growing database. Among the tables I use there is an 'order' table, containing all the details about placed orders, and an 'items' table for detailing the specifications on a kind of product we sell, like: | ID | order_ID | product code | quantity | width | height | price | and so on...

So far it has been sufficient use 'order_ID' as a foreign key to the orders table, so that one order can own multiple items. But once in a while we might sell other product with other characteristics requiring a new tables with different fields. Any number of these products could be placed under a single order.

Now I could make a pivot table with the 'order_ID' and the ID's of all the product tables, but I was hoping there would be a nicer way? Especially because I might add more product tables along the way but most of them will only seldom be used so many columns in the pivot table will be almost empty.

I've been struggling to find a nice design for this problem. I'm really hoping some of you guys have some good ideas :)