Re: Hierarchy of tables - Design problem

[Bob Field]

The structure I outlined did not take into account options. A different starter makes a different engine. It might make more sense to leave the starter out of the engine assembly and make it part of the genset assembly since which start you use is predicated on the genset in which it will be installed.

Or you can have two engine models, where the main subassembly is the engine less the starter, and then include electric or pneumatic starter as appropriate.

Either way you need information in PART and ASSEMBLY tables to accomodate this. Maybe you need a basic Engine Block assembly with the bulk of the common parts associated with it, then add variable items like starters, options for different kinds of fuel, etc.

As far as historical information is concerned, the order history should show the serial numbers of the units that were shipped, and the serial number table indicates exactly which part numbers made up a particular unit. When you make changes to a design, you issue new part numbers. Part numbers often have a revision code associated with them, so you know it's still basically the same part number but that an engineering change got implemented.

Subassemblies also have serial numbers, exact parts makeup, etc. A starter assembly might be manufactured and placed on the shelf. In the meantime an engineering change occurs but this starter is still a valid item, unless it is recalled or needs to be refitted before it can be added to an engine.

Hope this helps.