Re: Hierarchy of tables - Design problem
Posted by:
Bob Field
Date: July 05, 2006 09:40AM
Basically you are dealing with the fact that at this level, genset units, gensets, and engines are all parts assemblies. Parts assemblies can contain subassemblies ad infinitum. So at each level you will find both discrete parts as well as subassemblies. So keep your three tables as is, but introduce two new tables, parts and assemblies. The parts table is a straight list of parts regardless of the fact that they may be assemblies. Genset units, gensets, engines, crankshafts, bearings, nuts, bolts, all here. Then in the assemblies table have two columns: assembly_part and component_part. Assembly_part references a part, like a genset, that contains other parts. Component_part referes to a part that is a component of the assembly identified by Assembly_part.
THen in each of your tables Genset_Unit, Genset, and Engine, list the master part number for that unit.