Not sure how to have multiple categories of 'organisations' to a 'recipients' table

[pmhb2011]

Hi

Orange asked for some example data and your response to me is just a ramble which is difficult to follow.

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.

You say "These are some dummy records that illustrate this challenge (but don't reflect my db table structure or all of the different data and data types)"

It would help if you use the actual fields in your current tables.

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

I suggest you flesh out the following to help readers understand your environment and requirements.

We provide Grants to Recipients who undertake Projects.
Each Project has some proposed Outcomes/Benefits.
Each Project has associated Financial info.
Recipients could be an Org, a Division, a SubDivision, or a Unit
and may be Government, School, Church, Community Group...
Recipients are located in States, Districts, Towns..
Each Project has a Beneficiary
The system should allow for querying/reporting of Grants, Projects and Benefits.

How do Grants get awarded?
Is there an Application process involved?
How are Recipients chosen/selected/qualified?
How are Projects identified and described?
How are Project Proposed and Actual Benefits/outcomes set up and managed?
What level of detail is required?

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.

[pmhb2011]

OR... I've been reading a database design book this afternoon and I may have just had an epiphany... I think I'm thinking about this entirely backwards.

If a project's beneficiaries are the whole country, then that's the same thing as saying that the beneficiaries are in every town in the country. The challenge is really how to create a form that makes data entry easy if we need to select every town in the country or every town in a particular district (which is hundreds of towns). Or if we want to build a query to report on how many projects had a national scope.

I think I've been trying to come up with a design that will make queries and forms easier to build... Maybe I'm in the wrong section of the forum / asking the wrong questions.

Could it really be as simple as this?