Currently, I am trying to figure out what is a suitable way to design a data model about parts and processes that are needed to fabricate the parts. Several processes must be carried out by different machines. Sometimes two or more different processes must be carried out by a single machine along with other processes conducted by other machines.
For example, there are three different kinds of machines, m1, m2, and m3. Each of them can carry out different processes that differ in machine settings. My current idea is the use of the following tables:
CREAT TABLE m1 (m1-process-id, setting 1, setting 2, etc.)
PK m1-process-id
CREAT TABLE m2 (m2-process-id, setting 1, setting 2, etc.)
PK m2-process-id
CREAT TABLE m3 (m3-process-id, setting 1, setting 2, etc.)
PK m3-process-id
CREAT TABLE parts (part-id, other information about a part)
PK part-id
CREAT TABLE processes (process-id, m1-indicator, m2-indicator, m3-indicator)
PK process-id
if mx-indicator (x means 1, 2 or 3) is 0, it means that the corresponding machine is not involved.
One of the mx-indicator is set to the corresponding mx-process-id for the table m1, m2 or m3.
CREAT TABLE ProcessPart (process-id, part-id)
PK (process-id, part-id)
FK process-id form processes table and part-id from part table
I am not sure if I have made the right decision about the data model. Does anyone have some suggestions?
