[Data-modeling] Products with ingredients

[Tom Morris]

I think including processes is overkill for most uses.  If a
manufacturer cares about the process used to produce something,
they'll specify it and give things different part numbers for things
produced by different processes.

For non-foodstuffs, the list of "ingredients" for manufactured things
is often called a Bill of Materials (BOM) and everything is driven
part numbers.  Internally part numbers are assigned at a very fine
level of granularity with two nominally identical components
manufactured by different manufacturers being assigned different part
numbers so that they can be tracked.  Things which can change have a
revision code as a piece of their part number.  Parts which can be
substituted for each other are specified explicitly.  Obviously any
part can have its own BOM, so these can be nested to arbitrary levels.

The model number that a consumer sees like a Dell Dimension 7777 is
just the tip of the iceberg.  Internally within Dell, they know
exactly which plant manufactured a given instance, what rev etch was
used on the PCBs, what firmware was blasted into the ROMs, what
revision of which marketing collateral went into the box, etc, etc.
Something a simple as a new "Read Me First" letter in the box is under
revision control and will cause versions of part numbers to get
bumped.  On the other hand, things which are considered equivalent can
be substituted at will, so if they've decided that Samsung and Hynix
SDRAM can be substituted for each other, this will be mostly invisible
at the BOM level.

I imagine that pretty much the same thing happens for food.  What gets
called "high fructose corn syrup" on a product label could be a
variety of different ingredients from different sources that are
tracked very explicitly by the food manufacturer.  Some can be
substituted for each other and some can't.  As long as the nutritional
contents are within bounds, none of this is visible to the consumer.
It's only when there's a problem that people go back into the system
and track back to which lot of a specific ingredient from what vendor
was used in manufacturing a specific batch of food.

I think the level of granularity that you want to track all this stuff
at to start with is the end user level.  If you start getting into the
complexity that the manufacturers deal with, you'll get overwhelmed.

Tom