Forum Discussion
Helper IIIData Model - More than one fact table?
Helper IIIHi again
Yes, this is indeed the scenario and that does make perfect sense! How would you deal with the offence/outcome being applicable to to both the intervention table and the outcome FACT table? Does my design above look correct with the bridge between FACT tables? At the moment this is solely on outcomeID but I initially built the Outcome FACT table with the OffOutcome ID as a FK and could extend this out to the Intervention table also? It's dealing with multiple FACTs but shared dimensions that I am particularly struggling with!
Thank you again!
Resident RockstarHi,
The bridge table does look correct. It has been a while since i dealt with this particualr type of system but from what i remember they're not particualrly good at getting the info out in a good format. To join the Offence/Outcome to both the outcome fact and intervention is probably going to be tricky.
A lot of the time not much detail was needed from the intervention beyond start/end dates and how it closed. Each outcome should list an intervention id if it ended with an intervention so i'd bring that into the fact outcomes table and link the intervention to that. However, this could depend on the software, some of the providers were much worse than others and giving you access to the fields you need.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍