Gonna go with Apple pie since it is an easy enough example.
At the moment I have a db structure which lists the "apple pie" as an item and that item goes on a pallet. However, I'm coming up short for how to account for the whole enterprise side of this. The reason for the "enterprise" side of this is because the USDA (United States Department of Agriculture) will audit the "Apple pies"! Seriously!
I need to account for ingredients, supplies, and packaging.
Which includes having lot numbers (a way to track apple pies that are health hazards ... or pins), the boxes, the tins, the wrap, and all ingredients (including allergens) on top of which a way to consider if any of the materials are imported, domestic or for export (Hey! We want to share a major part of the American heritage with the world!)
An Apple pie consists of:
flour, butter, lard, apples, salt, sugar and cinnamon
* each of the above will need to keep the lot number in consideration ... example would be that if some tins that were rusty I could hit the db to find all items/products that have that issue. Another example would simply like butter was out of date (date checks will be done when pulling pallets from racks).
the tables I have so far:
items (all the above including the pie itself and a 5 pies for $19.99 option) .... this setup is really bothering me ....
pallets which keeps track of all the enterprise items. This would be like a pallet for flour, a pallet for butter ... etc ...
I keep debating if I should have a catalog which is a major dump all for everything and then have separate tables for that are on premise 1 for items (things that are like ingredients) and 1 for products (things that consists of ingredients or are actually finished like a quick hit for pie crust). Something like Pie crust can be either ordered as a finished product or can be made on premise.
Anybody have some ideas on how I should lay out my db structure better?