Thank you for taking the time to read through and reply despite feeling like it was "just a ramble". Apologies that it wasn't clear enough — I am new to this and I did try to be clear.
I did use the actual fields in my current tables. But:
- I did not use my current table structures because:
- I do not know the best way to structure the tables (this is the entire reason I joined this forum to ask this question)
- My current structure splits the records up across multiple, which I thought would be far more confusing to try and put up here!
- I left out a huge number of fields/data points that are not relevant to the part of the db I'm having difficulty designing. (I thought this would make the example records clearer and more relevant.)
- I did add the "direct recipient (Y/N)" column, which is not a field in the db. I did that because I thought it would be helpful in clarifying that
- Not all orgs/divisions/etc are recipients
- But if a recipient is a division/subdivision/etc of another org/division/etc the parent org/division/etc information will still need to be in the database.
Can you perhaps tell me exactly what else I need to do to be clearer (other than what Orange suggested below)?
Thank you for the advice.
You pretty much nailed it with what you wrote. I've made a couple of edits:
We provide Grants to Recipients who undertake Projects.
Each Project has some proposed Outcomes/Benefits.
Each Project has associated Financial info.
Each Project has beneficiaries.
Recipients could be an Org, a Division, a SubDivision, or a SubSubDivision/Unit of an Org.
Recipients have different "Types": Government, School, Church, Community Group, etc.
Recipients are located in Towns.
Organisations and their Divisions, SubDivisions etc are not always the same "Type" and are not always located in the same town.
Beneficiaries may be:
- people in a particular town (or group of towns)
- all people in a particular district (or group of districts)
- all people in a state (or group of states)
- all people nationally
The system should allow for querying/reporting of Grants/Projects according to:
- recipient type, including:
- recipient type broken down by parent organisation type where relevant
- beneficiary location, including
- Projects where all people in a whole state or a whole district were the beneficiaries
- Projects where beneficiaries were in any part of a specific state or district
The answers to your follow-up questions are not straightforward, but they aren't directly impacted by the heirarchical org/location puzzle that I'm trying to solve so I'd prefer to avoid going too far into them if possible...
I've attached a schematic of of how things relate - bearing in mind that:
- It's intentionally denormalised
- As I've said above, I'm not 100% sure if this is a reasonable way to handle the heirarchies
- I'm not sure whether it will be possible to query it in the way I want
- In reality the relationship beween projects and locations (beneficiary locations) is many to many, which this design does not currently represent.