I’d appreciate some advice regarding my data modeling.
I have 2 issues here I guess and I want to resolve these before I start to build reports.
Consider the data model in the attached image. This is only a part but it serves to illustrate my problems. The data is for a charity org.
Individual are godchildren, Customers are people who donate money to projects or/and godchildren. Projects are the different projects and Area are the different organizations that collect the money.
Customer can have agreements (Agreements table) to a project and/or a child in which case the donate a set amount each month. They can also give a one-time gift (Gift table).
I have relationships between my dimension tables. e.g. An individual is always linked to a project (Individual.ProjectId -> Project.Id). I cannot create an active relationship between Individual and Project as there is already one via the Gift table. Let’s say I want to create a report where I have the Project name in a slicer and the individuals in a table so I can select the project and see what individuals that are linked to it. How can I make that work if I don’t have an active relationship between project and Individual? I could merge the Project name into the Inidividuals table or I could turn on bi-directional relationship between Individual-Gift-Project tables but none of those alternatives seem right. I want to keep the project names in one dimension table and if I go down the road of bi-direnctional relationship I would have to use that between basically all dimension and fact tables.
I have relationships between my fact tables. The Agreement and Gift tables are related through Gift.AgreementId - Agreement.Id. A gift can either be a one-time gift to an individual or a project, in which case the AgreementId column is empty in the Gift table. Or, the gift can be a part of an Agreement where the customer donates a specified amount (Agreement.Amount) each month. In this case the Gift table contains the AgreementId for that transaction. Now let’s say I want to analyze the gifts collected as part of an agreement per area (an area is the organisation that collects the money). I would then need to go from Area to Agreement and then on to Gift to get the amount. A one-time gift does not have an area set (no areaid in Gift table). How should/could I model the information to support this? I have been thinking of merging the Gift and Agreement table but that does not seem “clean” as values in many columns in the resulting table would be valid only if agreementid is populated. The other option is to create some kind of link table consisting of Ids from all dimension and fact tables, but I haven’t tried that yet.
Appreciate any tips on how to model this effectively.