I am trying to set up a database for my family's hard cider business to track product costs. Cider is an assembled product. A base cider contains items like juice and yeast. A final CiderA in the bottle might have base cider, sugar, bottle, cork, label etc. Some items are used in multiple products. Some products are used in other products (like a gift basket)

I'm trying to figure out how to set up a schema to handle assembly of items that might refer to each other. Jonathan Stark had a good article up on this topic, but it seems to have disappeared in the last couple months (before I could digest it).

An example

CiderA contains

BaseCider1

sugar

bottle

cork

CiderB contains

BaseCider1

BaseCider2

bottle

cork

I'd like to have a layout that could assemble ciders or look up what items were used in a cider.

I've had trouble finding anything on the forum that quite applies to this. Any pointers or links to good articles would be appreciated.

Here's an old thread where we discussed inventory models. JS has taken his article off-line. Perhaps you can contact him. You might not need a recursive structure, though.