3

I'm working on a database which handles replacement of parts in a machine.

Rules

  • 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?

Current approach

    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.