0 Replies Latest reply: Jul 31, 2012 10:02 AM by Kathryn Brown RSS

    Data Modeling 3 tables with missing records in key fields

    Kathryn Brown

      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