Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15

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

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you model your proposed structures. Then, using some sample data and some sample scenarios,
    test/vet your model. Make sure you have a list of scenarios to test against the model. Here is a link to "Stump the model" that may be helpful.
    It is much easier to modify a structure on paper than in a physical database.
    How "fixed" is your proposed structure(requirement). What if you have a re-organization with sub-sub-sub.. division? Can you have a project with multiple organizations/types? How much detail of a "large company/organization" do you need? Do you have contact coordinates and associated details for Recipient?
    You might consider abstracting your situation to
    Recipient who:
    -has identified Contact details
    -receives Funds
    -has Project(s)
    -is a (Consultant, Small Business, part of a Large Entity,...) and adjust based on your testing of the model.

    Good luck.

  3. #3
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    Hey orange,

    Thanks for the response

    None of my structures are fixed.

    I don't feel like I'm quite up to modelling structures because I have genuinely no idea how to even design a usable structure. Even without modelling with data, I feel like my structure is too complicated / won't work. (I guess that's what I'm asking for help with is how to figure that out..!)

    I've done a bit more digging and have found a couple of ideas.

    Apparently what I'm talking about (wanting to use O001, D001 etc as multiple primary keys linked to a recipient ID foreign key) is called a "polymorphic association" and is something to avoid (which answers one of my questions / kills one of my ideas). From what I can find, I have 2 options. The first would be to have a recipients supertable that organises all the different categories (org,div etc). But because these are really heirarchies, rather than categories, the second option would be to have the table with self-joins.

    (I feel like I'd intuitively figured these out before but I now have names for them!)

    This led me to find a bunch of articles on adjacency lists vs nested lists, which confused the hell out of me (they're all super SQL heavy and I have only ever done a 40-minute online training on SQL, so I'm not quite there yet!!) Do you know of any advice for handling these in Access??? Or do I have to bite the bullet and learn SQL..?

    Do you have any advice about what best practice is, or how you would tackle this kind of puzzle??

    (This is my first database and I've only done a bunch of online (LinkedIn Learning) training about database design and Access, so I'm very much just treading water to keep my head up at the moment!)

    I have a similar issue with geographic regions as well. At the moment I'm looking of splitting it over separate tables (E.g., States, Districts, Towns) but now I'm wondering if that should be table of just locations with parent/self-joins, because sometimes I need the flexibility to pick a state instead of drilling down to town level. But I still need to be able to identify all of the money that went to a particular state (whether it was state-level, district-level, or town-level funding) and I'm not sure if I have the querying skills to do that in a single table with self-joins. (Does that make sense??)

    Thanks again for your help! It's very much appreciated...
    Last edited by pmhb2011; 09-29-2020 at 05:49 PM. Reason: filled in missing words

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The quickest advice I can give is to direct you to the link in my signature on Database Planning and Design. And within the materials --work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned. Once you have worked through these tutorials, use the same technique with your own set up. That will get you a data model. Now exercise that model with some test data(good and bad) and some test scenarios.
    Good luck.

  5. #5
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    Hi again,

    Thanks for your advice. I've had a scan through the topics on Roger's blog (thank you for pointing me at that - it looks amazing) and watched the Stanford Dbclass videos on relational design and functional dependencies (super theoretical and give me a new level of respect for database developers!).

    I may have misexplained myself. It's not that I have no idea how to design a usable database structure in general. When I say I've done a bunch of online tutorials, I mean that I've done 40+ hours that included theory and practical exercise files. (I had to find something to do during 27 weeks (and counting) of working from home / lockdown!!!)

    It's just this specific scenario that I'm coming across over and over that deals with hierarchical where I need to be able to refer to different levels in the heirarchy for the same purpose that I'm having trouble with. I don't know whether it's best to put them into separate tables (which feels like it satisfies all of the normalisation rules but (as far as I can tell and please correct me if I'm wrong) makes it impossible to choose as a location any of state or district or town):

    tblState
    ---
    StateID
    StateName

    tblDistrict
    ---
    DistrictID
    DistrictName
    StateID (FK from tblState)

    tblTown
    ---
    TownID
    TownName
    DistrictID (FK from tblDistrict)


    Or whether to put them in 1 table, which lets me identify heirarchy; lets me choose any of state, district, or town as a location; has flexibiliy in terms of numbers of level of hierarchy (which is good for the organisations scenario); but doesn't separate the locations out into groups of other like locations (again, I'm not sure if I'm correct or making complete sense).

    tblLocation
    ---
    LocationID
    LocationName
    LocationParent (FK from tblLocation/self-join)


    Both seem to have pros and cons and I foresee both having different difficulties when it comes to forms and queries that I'm not sure about.
    I really need someone with experience to chat to about which might be best and why / pros and cons of both... I've done hours of online stuff and none of it has spoken to this specific scenario...

    Any feedback on this specific scenario would be highly valued and appreciated.

    tblOrganisations

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Having the structure of tblFundingRecipients like tblFundingRecipientsn: FundRecipID, ProjectID, RecipientType, RecipientID, where RecipientType has values like 1 - organisation, 2 - division, 3 - subdivision, 4 - unit, and RecipientID is FK based on OrgID, DivisionID, SubDivID or UnitID depending on value of RecipientType os one way to go. The only problem is, there is no easy way to join those tables in Access. Had you your BE as SQL Server database, you could use the syntax like
    Code:
    SELECT
         proj.Project,
         (CASE rec.RecipientType WHEN 1 THEN org.OrgName WHEN 2 THEN div.DivName WHEN 3 THEN sub.SubDivName WHEN 4 THEN unit.UnitName LSE Null END) AS RecipientName
    FROM 
         tblFundingRecipients rec 
              LEFT OUTER JOIN tblOrganisations org ON org.OrgID = rec.RecipientID AND rec.RecipientType = 1
              LEFT OUTER JOIN tblDivisions div ON div.DivisionID = rec.RecipientID AND rec.RecipientType = 2
              LEFT OUTER JOIN tblSubDivisions sub ON sub.SubDivID = rec.RecipientID AND rec.RecipientType = 3
              LEFT OUTER JOIN tblUnits unit ON unit.UnitID = rec.RecipientID AND rec.RecipientType = 4
              LEFT OUTER JOIN tblProjects proj ON proj.ProjectID = rec.ProjectID
    , but Access doesn't support such syntax. There is a roundabout joining subqueries instead of tables, but the query will be clearly cumbersome!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't know that there is any structure to support a hierarchy that is better than any other without some experimentation. I have not found many examples of hierarchical structures that are applicable generally. There was an example at UtterAccess that I referred others to previously, but, with the new design at UA, I can no longer find the reference to wiki articles.
    I recommend that you research "implementing hierarchical database in ms access" or similar. This may give you some examples to review/study. There are some theoretical articles by Joe Celko that are often cited. But may not be applicable to Access implementation as Arvi has mentioned.
    Also, you could describe the requirement with some detail and provide some "mock up" data for the various entities in your description - AND - supply some mock up of the required output formats (reports/query(s)/form(s) or whatever). Some readers may have the time to investigate your requirements and do some experimentation and provide you with more focused responses/structures.

    Bottom line is -in my view - there are not many examples of hierarchical databases/structures; even fewer that have general applicability; and even fewer using MS Access. Probably more effective to model your situation and vet your evolving model with sample data by ensuring sample test scenarios are accommodated. It is so much easier to change a design of a model/prototype than a physical database that I encourage you to do your analysis and design to get a blueprint for the database you will build.

    Good luck. And if you get an approach for your situation, please comment in the forum since such examples seem to be few and far between.

    Here are a few links found via google - the responses/discussion may offer some insight:

    https://www.tek-tips.com/viewthread.cfm?qid=1516577
    https://www.access-programmers.co.uk...rarchy.293348/
    https://dba.stackexchange.com/questi...cture-database
    https://stackoverflow.com/questions/...evels/54545454
    Last edited by orange; 09-30-2020 at 06:36 AM. Reason: spelling

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Do you want to be able to :-

    1. Select a State
    2. Then only see a list of Districts for the State Selected.
    3. Then be able to select a District Name and then see only those Towns in the District Selected?

    If this is the case then you just need Cascading Combobox's
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    Quote Originally Posted by orange View Post
    I don't know that there is any structure to support a hierarchy that is better than any other without some experimentation.
    ...

    Bottom line is -in my view - there are not many examples of hierarchical databases/structures; even fewer that have general applicability; and even fewer using MS Access. Probably more effective to model your situation and vet your evolving model with sample data by ensuring sample test scenarios are accommodated. It is so much easier to change a design of a model/prototype than a physical database that I encourage you to do your analysis and design to get a blueprint for the database you will build.

    Good luck. And if you get an approach for your situation, please comment in the forum since such examples seem to be few and far between.
    Hi again, Orange,

    Thank you for your patience and continued input! I spent a fairly large chunk of yesterday on Roger's Access Blog. I'll have a look at those sites over the next day or so.

    I'm more than happy to spend most of my time in the design phase to make sure when I finally put it into Access, it will actually work.

    I'm starting to wonder whether this might be one of those situations where breaking normalisation rules would be justified to build a supertable or a relations table that can be used generically as "Location" or "Recipient" that will house data that looks like:

    ID Location State * District Village
    1 State A State A
    2 District A State A District A
    3 Village A State A District A Village A
    4 Village B State A District A Village B
    * FK from State table
    † FK from District table
    ‡ FK from Village table

    The State, District, and Village fields would get their information from normalised tables and have constraints disallowing districts/villages from being put in the wrong states/districts, and disallowing duplicates of State+District+Village.

    I know that denormalisation isn't ideal but, in this case, it will make it possible for 1 FK to point at any level of the heirarchy, (hopefully, maybe?) make it easy for end-user data entry, and have enough flexibility to be able to query (for example) how much funding is going to particular places at any level (e.g., I'd want any funding going to Village B to turn up in a report about State A).

    Still thinking about it (and always open to feedback/suggestions), though...
    Last edited by pmhb2011; 09-30-2020 at 08:02 PM. Reason: Fixed typo that completely changed meaning

  10. #10
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    Quote Originally Posted by mike60smart View Post
    Hi

    Do you want to be able to :-

    1. Select a State
    2. Then only see a list of Districts for the State Selected.
    3. Then be able to select a District Name and then see only those Towns in the District Selected?

    If this is the case then you just need Cascading Combobox's
    Hey Mike,

    Thanks for your response.

    Not quite. I mean, yes, those things need to be controlled, but my main difficulty is that:

    - State, District, and Town are all in separate, normalised tables AND
    - I have other tables that require a location that could be a state OR a district OR a town

    So I'm trying to find a way to design the database that follows normal forms and allows for this functionality. I am slowly coming to the conclusion that doing both easily is mutually exclusive.
    Last edited by pmhb2011; 09-30-2020 at 08:03 PM. Reason: Changed my mind about something

  11. #11
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    Quote Originally Posted by ArviLaanemets View Post
    Having the structure of tblFundingRecipients like tblFundingRecipientsn: FundRecipID, ProjectID, RecipientType, RecipientID, where RecipientType has values like 1 - organisation, 2 - division, 3 - subdivision, 4 - unit, and RecipientID is FK based on OrgID, DivisionID, SubDivID or UnitID depending on value of RecipientType os one way to go. The only problem is, there is no easy way to join those tables in Access. Had you your BE as SQL Server database, you could use the syntax like
    ...
    , but Access doesn't support such syntax. There is a roundabout joining subqueries instead of tables, but the query will be clearly cumbersome!
    Thanks for your feedback, Arvil. I am pretty motivated to avoid combersome queries as much as possible!!

    Access is all I have access to at the moment, (no pun intended), so I'll have to work within its functionality.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As June often says
    Normalize until it hurts and denormalize until it works.
    Can you give us 10 records representing test data --does not have to be real data real people? You can use names like Porky Pig, Paige Turner.. or related entities Big Project locations UpState, Central State, MyTown... Backwoods District.... and some idea of outputs expected. Again we're talking overview/big picture in order to "experiment". Readers need some "sample data". It's a little like sailing --there's lots of theory and good intentions while onshore, but in reality you have to get on the boat and get a little wet to learn.

    We're still talking basic table design and relationships, not final anything.

    As per the Rogers examples, can you provide a description of the business so readers can use a few sample records to flesh out the description with a first approximation model.

  13. #13
    pmhb2011's Avatar
    pmhb2011 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    15
    "Normalize until it hurts " sounds about right

    It's for a grants administration organisation in a developing country context. They distribute funding to anything from small community groups/school sports teams/specific classrooms to government ministries for various projects.

    (Btw, I'm volunteering and (clearly) not a database developer.)

    So far my preliminary design has about 28 tables, including look-up tables but not including organisation sub/sub-sub/etc division tables or strategic plan tables. (As you can imagine, I am absolutely not thinking about actually starting to build it until I'm confident the design will work).

    The part I'm really struggling with, as I said, is the heirarchical stuff, so I've added some extra tables to illustrate why it starts to do my head in very quickly.

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


    Projects

    ID Project Name Recipient Recipient Type Recipient Location Beneficiaries Location Amount
    01 Big Project Ministry of Excellence Government Capital City Whole Country $100
    02 Rural Women's Happiness Project Sorrowton Women's Wellbeing Community Group Community Group Sorrowton Sorrowton $100
    03 Terrible Project We Do Things Badly Pty Ltd Business Horrortown District of Unspeakable Horror $100
    04 Rain Generateion Project Sleet Division Government Capital City Dry State $100
    05 Amazing Project Stop the Rain NGO Raintown Wettest District $100
    06 Evaluation Project Evaluation Team Government Capital City Whole Country $100
    07 Excellent Children Project Capital City State School School Capital City Capital City $100
    08 Better-than-Excellent Children Project Sorrowton Elementary School School Sorrowton Sorrowton $100
    09 Healthy Hopefulness Project Despairton Church Church Despairton Despairton $100
    10 Men's Hope Challenge Men's Wellbeing Committee Community Group Sorrowton Sorrowton $100



    Locations

    Location State District Town
    Whole Country
    Capital City Main State Governance District Capital City
    Dry State Dry State
    Wettest District Wet State Wettest District
    District of Unspeakable Horror Awful State District of Unspeakable Horror
    Despairton Awful State District of Unspeakable Horror Despairton
    Horrortown Awful State District of Unspeakable Horror Horrortown
    Sorrowton Awful State District of Sorrow Sorrowton
    Raintown Wet State Wettest District Raintown

    Example queries to report on:

    • Funding towards projects that benefited the whole population (even though "whole country" doesn't have any state etc data)
    • Funding towards projects that benefited the all people in the District of Unspeakable Horror (only Project 03)
    • Funding towards projects that benefited anyone in the District of Unspeakable Horror (Projects 03 and 09)



    Organisations/Recipients
    Organisation Sub-division Sub-sub-div Sub-sub-sub div Type "Direct Recipient" *
    Church of No Hope Church
    Church of No Hope Despairton Church Church Y
    Church of No Hope Sorrowton Church Church
    Church of No Hope Sorrowton Church Men's Wellbeing Committee Community Group Y
    Church of No Hope Sorrowtown Elementary School School Y
    Ministry of Education Schools Division Government
    Ministry of Education Schools Division Government
    Ministry of Education Schools Division Capital Sity State School School Y
    Ministry of Everything Government
    Ministry of Everything Weather Department Government
    Ministry of Everything Weather Department Sleet Division Government Y
    Ministry of Excellence Government Y
    Ministry of Excellence Great Division Government
    Ministry of Excellence Great Division Community Unit Government
    Ministry of Excellence Great Division Community Unit Sorrowton Women's Wellbeing Community Group Community Group Y
    Ministry of Excellence Great Division Greatness Unit Government
    Ministry of Excellence Great Division Greatness Unit Evaluation Team Government Y
    Ministry of Extermination Government
    Ministry of Extermination Mouse Division Government
    Stop the Rain NGO Y
    We Do Things Badly Pty Ltd Business Y
    * FYI / for clarity only - not an actual field anywhere ever

    Example queries to report on:

    • All Government Projects (Should include Project 07 at Capital City State School because it's parent org is Government, even though the recipient type is "School" and Project 02 at Sorrowton Women's Wellbeing Community Group, even though the recipient type is "Community Group")
    • All School Projects by type (Projects 07 and 08) †
      • Government Schools (Project 07)
      • Church Schools (Project 08)

    • As for School projects but for Community Group projects


    † This is tricky because the schools/recipients themselves are at different levels of the heirarchy. In the case of non-church private schools (not in this example) they may be the top-level organisation.

    The Outcomes are a similar situation (Outcome 1 covers 1 and 1-1 and 1-1-1 etc) but I think this might be enough to get the idea!
    Last edited by pmhb2011; 10-01-2020 at 08:28 PM. Reason: Minor edits

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

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

    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.

    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Email To Using Multiple Recipients
    By burrina in forum Sample Databases
    Replies: 1
    Last Post: 10-08-2019, 12:43 PM
  2. Sending e-mail to multiple recipients
    By riggsdp in forum Programming
    Replies: 4
    Last Post: 12-01-2014, 12:45 PM
  3. email multiple reports to multiple recipients
    By slimjen in forum Programming
    Replies: 5
    Last Post: 08-31-2014, 11:52 AM
  4. Replies: 3
    Last Post: 09-18-2013, 09:25 AM
  5. Replies: 2
    Last Post: 10-24-2012, 10:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums