I'm working on a database which handles replacement of parts in a machine.
- A machine has many components
- A machine has a set of assembly instructions
- Many components have many part (screws, bolts, etc)
- Parts can substituted by components, there are no constrictions on components to parts they substitute.
- Components behaving as substitutes require a special set of instructions
Bonus: Components can also swap out components
I've currently implemented polymorphism. Below is the sketch, I'm curious in other approaches. While I've scratched at Martin Fowler and some other literature, I've not been able to complete a visual of another approach.
I'm primarily dissatisfied with the duplication of fields for component_parts, and substitute_parts.
How would I go about establishing a schema achieving the above rules, while avoiding a straight shot towards polymorphism?
machine -id (pk) -name machine_directions -id (pk) -machine_id (fk) -step -direction components -id -machine_id (fk) -name component_parts -component_id -part_id -part_type (part, or substitute_component) -amount substitute_component -id -substitute_part_id (fk to relate to part) -name substitute_directions -id (pk) -substitute_component_id (fk) -step -direction substitute_parts -component_id -part_id -amount parts -id -name