Data Modeling 3 tables with missing records in key fields

[#1]

Hi

I have searched the community and read the best practice documents but am still unsure of how to progress - any help welcome.

I have 3 tables that I need to associate. They represent different levels of detail:

1. Jobs - mainly dimensional information but will want to use this table as the source for unique counts etc
2. Operations - combination of dimensions and metrics
3. Materials - combination of dimensions and metrics

• Each record in Jobs can relate to many in Operations - all records in Operations relate back to a record in Jobs (Association is via job number and is simple and works)
• Each record in Operations may relate to many in Materials (Association is via job number and operation number. Results in exclusion of the records in the last bullet point)

• Some records in Operations do not relate to records in Materials (this has no impact on my current model but means I can't change the order of associations between the tables)

• Some records in Materials do not relate to records in Operations but do relate to records in Jobs e.g. these are unplanned use of materials in our system - these are low level but important to the business. (Current model excludes these records since operation number is null)

I have considered using a link table between Jobs and the Operations/Materials table but believe I would lose the assocation I need between Operations and Materials.

My current thinking is to create records in the Operations table for those missing in the Materials table so that the association works. I would do this by concatenating a table of Materials where operation number is null with the Operations table.

Is there a better way of doing this?

Thanks