Search code examples
databaseschemanormalizationscd

Problems defining normalized SQL database schema with recursive relationships and empty values

I have a question regarding the correct implementation of a Schema that I'm currently wrecking my head with:

We have machines, which consist of components, which consist of parts.

However, the relationships are as follows:

  • Machines (1) --> Components (N) - a machine is made up of various components
  • Components (N) --> Parts (N) - a component is made up of multiple parts, a part may be used in multiple components
  • Components (N) --> Components (N) - a component can also be made up of other components
  • Machines (N) --> Parts (N) - Some parts may also be directly assigned to a machine

Furthermore, both parts and components that are flagged as needs_welding=1 will have a price associated with them. These prices will change over time.

I'm not quite sure as to how to model the following aspects:

  1. How to relate the Parts directly to the machine table
  2. How to model the parent/child relationship between the components
  3. How to attach prices to the items (kinda reminds me of an SCD in a DWH, but I cannot seem to patch it together)
Solution

  • A good solution for N->N mappings is to create a specific mapping table. So, for example, to map a Component to the Part(s) it is made of, you can create a table called something like MapComponentToItsParts, which has two columns, the first which contains the ID of the component, the second which contains the ID of the part. They should each be Foreign Keys to their respective tables. You can create similar tables to MapComponentToSubComponent, or MapMachineToPart.