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:
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)
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?