I have a data model where i have 4 fact tables with transactions
Incident Data, Injury Data, Safety Rules data and cause analysis data. The incident fact table has hierarchy over the injury table, it is linked by the form number and injury classification, the cause analysis and safety rules table can link to both the incident and injury tables.
Some of the supporting tables can relate to all 4 fact tables and others are specific to a particular table for example the division, region, branch tables support all 4 fact tables but the injury classification, injury type supporting tables only relate to the injury tables.
My current data model looks like this there is one fact table missing which i have yet to add in, it largely works in terms with being able run calculations but i feel i can simplify the model to optimise the performance.
Current model design
i have drawn a rough sketch on how i think i can simplify the model, what i am unsure of is whether i need to link the supporting tables that are currently only linked to the Incident table to also link to the Injury, cause and safety tables. And will my 1 to many relationship from the incident table to the injury table an the incident to the cause analysis table and the safety tables work, these are 1 to many linked by a unique form number.
i’ve read the other forums on this subject and i think im close but would like some feedback from the forum experts