Hi,
I'm trying to design a database that (among other things) will say who received project funding.
Funding can go to a variety of different "types" of recipients:
- Consultants (individuals)
- Small businesses / organisations
- Large organisations/companies that have:- Divisions that may or may not have:
- Sub-dividions that may or may not have:
- Sub-sub-divisions (I'm calling "Units")
All of the different levels of an organisation might be a funding recipient.
To normalise the organisation information, I am toying with something like:
tblOrganisations
---
OrgID
OrgName
OrgType (FK)
OrgLocation (FK)
tblDivisions
---
DivisionID
DivisionName
OrgID (FK from tblOrganisations)
DivisionType (FK)
DivLocation (FK)
tblSubDivisions
---
SubDivID
SubDivName
DivisionID (FK from tblDivisions)
SubDivType (FK)
SubDivLocation (FK)
tblUnits
---
UnitID
UnitName
SubDivID (FK from tblSubDivisions)
UnitType (FK)
UnitLocation (FK)
Then, I would have some other table like
tblFundingRecipients
---
FundRecipID
ProjectID (FK)
RecipientID (FK)
In reality, the recipient could be an Org, a Division, a SubDivision, or a Unit but I don't think that's possible to set up in Access (where RecipientID would be a FK that could be from one of any of OrgID, DivisionID, SubDivID, or UnitID). I tried to make something work by putting a letter on the beginning of the autonumber ID "O001, D001, S001, U001" to differentiate them, but Access doesn't seem recognise the prefix as part of the ID).
The "types' complicate if futher. For example (this isn't a real example):
Recipient 1 — Org: Private school, Type: Educational Institute
Recipient 2 — Org: Catholic Church, Type: Church | Division: Catholic-Church-Owned Shool, Type: Educational Institute
Recipient 3 — Org: Education Dept, Type: Government | Division: State School, Type: Educational Institute | Sub-Division: Prep Classroom, Type: Educational Institute
In case 1, the org is the recipient, in case 2, it's the division, in case 3, it's the sub-division. How could I query what what type of recipients received funded if the recipient FK is always connected to the organisation level? (Instead of 3 x educational, I would get 1 x ed, 1 x church, and 1 x gov).
Would I be better off having 1 organisation table with self-joins indicating when an organisation is a parent of another?
This is starting to do my head in! Any advice, thoughts, or feedback would be appreciated