Jump to content

Photo

Recommend? enterprise level "apple pie" db structure


  • Please log in to reply
3 replies to this topic

#1
SapporoGuy

SapporoGuy
  • Members
  • 1,970 posts
  • Joined: 10-June 11
  • LocationSapporo Beer Factory

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

a box

a tin

plastic wrap

* 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. 

 

ugh :(

 

 

Anybody have some ideas on how I should lay out my db structure better?

 



#2
ianonavy

ianonavy
  • Members
  • 921 posts
  • Joined: 14-April 10
  • Expertise:HTML, CSS, Java, Javascript, Python, SQL
It depends on what you want to do with the data. If you just care about minimizing redundancy, then try to make each table follow the structure of the data. For example, your pie table would have foreign keys to the ingredient tables that hold records for each instance of an ingredient. Your rows would correspond to the finest granularity you can get for each table. For example, you might have a "tin" table with columns for whether or not it was rusty where each row was 1 tin. Or if you don't have that fine granularity, you can make each row one pallet of tins and just have a count for the number of rusty tins. Then to represent shipments, you would have a box table, and every pallet of pies in the pie table would have some foreign key to the box ID. This is the approach you would take if you're doing a lot of INSERTS but few and far between SELECTS.

If what you're mostly doing is trying to represent dimensional data for querying and analysis, then you'll want to take a leaf out of OLAP. Have a bunch of "attribute" tables that encapsulate different dimensions of your data, and then have a centralized "fact" table that is the intersection of everything. For example, you might have a "tin" table and a "box" table and a "butter" table, and for every combination of "tins", "boxes" and "butters", there may or may not exist a row called "pie" which has all the facts you could ever need including sales prices and distribution statistics. If you want the average price of a pie made from a specific box, you would select all pies where the box ID is a specific value. If you wanted all the pies with the rusty tins, you select from tin where rusty is true and join the pie table on that. You could do this for multiple dimensions as well: select tin where rusty and distributor where state is California, join pie and see the average price of all rusty pies sent to California. You could even build pre-computed aggregation tables that combine multiple dimensions together to provide just the necessary statistics for common queries.

#3
SapporoGuy

SapporoGuy
  • Members
  • 1,970 posts
  • Joined: 10-June 11
  • LocationSapporo Beer Factory
Ugh! iOS Safari is seizing up on me like 6 times now.

Thanks for the ideas.
I'm gonna potlat back from my desktop since Ibro t want to rewrite another post :(

#4
SapporoGuy

SapporoGuy
  • Members
  • 1,970 posts
  • Joined: 10-June 11
  • LocationSapporo Beer Factory

After thinking over what you wrote, this is the setup I came up with:

 

Catalog - A baseline table that contains all static information about items such as allergens and model # and such

 

Items - relates on catalog_id, and relates to the lot_numbers table and pallets. This is sort of like your OLAP central table

 

Lot_Numbers - a table to contain all lot numbers for items and products. Products will have multiple lot numbers. This table will also be used for scanning when "rusty tins" pop up.

 

Pallets - relates on item_id and contains dates for packing and expiration. Also will have it's own lot_number (not sure if this going to be used or not).

 

I debated about actually differentiating between items and products (items as ingredients and product as pies) but the main situation is that everything is essentially the same when stored on a pallet.

 

* I normally like to keep my columns pertinent and will create an excessive amount of tables to keep my tables slim but the above shouldn't be too much of an issue. 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users