I have a table with 2 key columns. The 2 key columns are a lookup into a 3rd, the 3rd column is the "answer".
This is a mechanical factory type application.
The first two columns are SAP codes (8 character codes) for physical objects. The 3rd column is the time required to fix the two physical objects together.
For example here are two rows from the table
Thing1 Thing2 Time to glue together
TUBE5678 TUBE3241 33
VALV5678 TUBE3311 102
VALV5678 VALV3311 102
Now my problem is that some 8 character SAP codes correspond to tubes of varying diameters, thicknesses and materials and they exist in the Tubes table.
Other SAP codes are distinct objects (valves etc), in an Objects table.
I.e. the first two columns are foreign keys into other tables.
It is mechanically possible to fix:
- a tube to a tube
- an object to an object
- a tube to an object.
Before the introduction of VALVes it was easy, each 8 character code in the first two columns corresponded to a single row in the Tubes table.
But with the introduction of VALVes I'm not sure how to keep the RDB database design "clean", because the 8 character code could be in the Tubes table or in the Valves table.
I'm no DB expert, so thanks in advance for any help or pointers
Owen