I am currently working on a project whereby I am linking in MS Access the data in the "MTL_SYSTEM_ITEMS_B" Table, "BOM_OPERATIONAL_ROUTING" Table, "BOM_OPERATION_SEQUENCE" Table and "BOM_OPERATION_RESOURCES" Table. As for the link between the "MTL_SYSTEM_ITEMS_B" table and the "BOM_OPERATIONAL_ROUTING" table, there is a one to one relationship. Though for the link between "BOM_OPERATIONAL_ROUTING" (link from) and "BOM_OPERATION_SEQUENCE" (link to), this link is not so straight forward. The BOM Technical Manual shows that these tables are linked by way of the field "ROUTING_SEQUENCE_ID".

What makes things complex is that there is a "COMMON_ROUTING_SEQUENCE_ID" field for parts that have similar routings. For parts that have similar routings, the valid routing is the "COMMON_ROUTING_SEQUENCE_ID". The question I have is what type of database structure should exist as there are two fields that are possible candiates for linking. When looking at the data in the "BOM_OPERATIONAL_ROUTING" (link from) table in Discoverer, the data in the "ROUTING_SEQUENCE_ID" field is unique, though the data in the "COMMON_ROUTING_SEQUENCE_ID" field is not unique. The field "ROUTING_SEQUENCE_ID" does not contain data in the "BOM_OPERATION_SEQUENCE" (link to) table where data exists in the "COMMON_ROUTING_SEQUENCE_ID" field for the part in "BOM_OPERATIONAL_ROUTING" (link from) table. In other words if you simply link the field "ROUTING_SEQUENCE_ID" that exists between "BOM_OPERATIONAL_ROUTING" and "BOM_OPERATION_SEQUENCE" you will not get operation sequence records for parts that have common routings.

It is confusing as the Oracle Technical Manual does not spell out this issue in the Diagram 9:Routings or I do not understand how to interpret the relationships.

Any help in defining the relationship between "BOM_OPERATIONAL_ROUTING" table and "BOM_OPERATION_RESOURCES" table would be greatly appreciated.