Data Modeling 3 tables with missing records in key fields

Tue, 31 Jul 2012 14:02:44 GMT

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:

Jobs - mainly dimensional information but will want to use this table as the source for unique counts etc
Operations - combination of dimensions and metrics
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

topic Data Modeling 3 tables with missing records in key fields in QlikView

Data Modeling 3 tables with missing records in key fields