[eluser]jroot[/eluser]
Imagine that you have a products table in a manufacturing business. Those products can be sold individually. However, in some cases those products are used like raw material to construct other products.
For example, say you have an L.E.D. light that can be sold to a customer. That same LED can be used in the construction of another product that we call an assembly.
Modeling this in CI or in the DB isn't tough. The problem comes in when an assembly of products is used to create another assembly product. In theory, you could have assemblies of assemblies of assemblies... infinity. Worse yet, multiple assemblies can be used to create other assembly products.
For example, a battery, wire, LED and solder are individual products that can be combined to create a lighting assembly. Wheels, frame and body can create a model car assembly. A car with lights assembly would be the lighting assembly plus the model car assembly.
This example, a battery, wire, LED, solder, wheels, frame and body would all be products with part numbers. The lighting assembly and the model car would each have part numbers. Finally the car with lights assembly would have a part number.
I could really use some help with a schema that make some sense. I have been thinking that you would have a base_products table with productIDs. The products would relate to an assemblies table that have assemblyIDs. I could add a column to the assemblies table that has the id of another assembly, but I wouldn't be able to create an assembly product that is comprised of multiple assemblies...
It feels like 4th normal form is necessary (a table of ids to keep all of the relationships in order), but I don't have a huge amount of experience at this level of recursion... Any advice would be great!
