Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Simple table relationships ("faces" to "spaces" to "chairs")

    Experts:



    I am currently working on a redesign of a organizational database. Besides that there is some requirements planning left (i.e., what are the specific functions/processes), I wanted to run the basic architecture by you in hopes to obtain some recommendations for linking tables appropriately.

    Background:
    - I have an organization that has several tiers/levels ("office names" are on different levels such as principal level; division level; branch level, etc.)
    - That said, the office names/organization structure is captured in the [Organization] table with [OrgIDpk] as the primary key. Office names may be referred to as "spaces".
    - Naturally, we have an [Employees] table with [EmployeeIDpk] as the primary key. Employees may be referred to as "faces".
    - Then, we have a [JobPosition] table with [JobPositionIDpk] as the primary key. Job positions may be referred to as "chairs".

    Concept:
    - The organization (i.e., the individual offices) has multiple employees
    - Also, the organization has multiple job positions
    - While job positions could be moved up/down or across the organization, so can employees (i.e., changing positions within another branch of the organization).

    Generally, the first thing that comes to mind is to have a 1:M relation between [Organization] and [Employees] via the OrgIDpk primary key and [OrgIDfk] foreign key. That is, [Organization].[OrgIDpk] to [Employees].[OrgIDfk].
    Likewise, I would think that the same can be done between between [Organization] and [JobPosition]. For example, [Organization].[OrgIDpk] to [JobPosition].[OrgIDfk].

    My questions:
    a. Based on the above concept, I will have a two (2) "cascading" relationships from 1) [Organization].[OrgIDpk] to [Employees].[OrgIDfk] AND 2) [Organization].[OrgIDpk] to [JobPosition].[OrgIDfk]. Could that pose a problem?
    b. I presume I could then link "faces" to "spaces" and -- through that -- to "faces" to "chairs". Is that a correct assumption?

    Alternatively, if you have an alternative recommendation to link these three tables differently, I'm open to suggestions.

    Thank you,
    EEH

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The base tables you need are going to be Jobs, Offices, Employees .
    Your Chairs table is actually going to be a junction table of foreign keys from Jobs, Offices and Employees , where presumably/possibly the employee could be blank indicating a position to be filled?

    Does that cover all the eventualities ? Multiple jobs at multiple offices.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty - thank you for the prompt response... 'very much appreciated.

    Follow-up though... doesn't the junction table mean an "employee can have multiple jobs" and a "job/position" (the specific job such as JOB ID #) can have multiple employees associated with that?

    I can see to have a junction table between "working groups" and "employees". That is, working groups will have multiple employees and employees may be assigned to multiple working groups.

    Otherwise, please find attached a **high-level** example with a junction table between employees and jobs. How do I tie in the organization into it?

    Thanks,
    EEH
    Attached Files Attached Files

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

    You could also have a design like the ER Diagram attached
    Attached Thumbnails Attached Thumbnails er.JPG  

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Mike60smart:
    Thank you for sending the ER diagram.
    So, based on my scenario, I would replace both the [tbluDivsions] and [tbluBranches] with single table [tlbOrganization]... is that a correct assumption?
    Thank you,
    EEH

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

    Are you saying that the Organisa1ion HQ has a specific number of Employees.

    The Organisation has a number of Divisions with each Division having a number of Employees.

    Each Division has a number of Branches with each Branch having a number of Employees.

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

    Org

    HI

    You could also have a structure like this:-
    Attached Thumbnails Attached Thumbnails er.JPG  

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    mike60smart:

    Thank you for posting the ERDs... very helpful.

    A few days ago, I did ask a question how to tie in employees into a multi-tiered, cascading organization (divisions, branches, sections). Multiple experts encouraged me to merge the different org levels into a single table. Please see
    https://www.accessforums.net/showthread.php?t=77601 for additional details.

    After some long deliberations, I attempted to replicate the recommended process and I'm very glad I did. There are some major benefits having all "offices" (i.e., the "CEO-office" and the "down-in-the-weeds analytics office") in the same table. I don't want to reopen this long discussion on this... so right, fact is that I do **NOT** have to worry about tblDivision vs. tblBranch vs. tblSection any longer.

    I'm still a bit confused about the junction table. So, let me recap:
    - The organization has multiple offices that are stored in table [Organization]... that's what I called the "spaces"
    - Each "office space" has multiple jobs to support their individual function... that's what I called the "chairs"
    - The organization multiple employees... that's what I called the "faces".

    Please see attached JPG... why wouldn't this work. Pls refer to additional details in the Word document (in Zip file).

    Thank you
    EEH
    Attached Thumbnails Attached Thumbnails ERD.JPG  
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    tblOrganizationUnits
    as already described

    tblJobs
    JobID
    JobTitle
    OrgIDfk

    tblEmployees
    EmpID
    EmpLast
    EmpFirst
    JobIDfk (or maybe EmpID could be saved in tblJobs)

    Employee changes job then change JobID in Employees. However, if you care about history of jobs employees held then need another junction table.

    tblEmployeeJobs
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 -- thank you... I apologize but I'm have a very visual person. Would you be willing to put this in a diagram (with primary key/autonumber and linked to foreign key/number). That would GREATLY help me out.

    And, yes, we don't need to track about an employee's old positions. Only tracking current one. Again, simple diagram would help (picture is worth a thousand words).

    Thank you in advance,
    EEH

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I'll let you attempt diagram and provide effort if you encounter issue. Why can't you just build the tables with fields I suggested? Seems rather explicit.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7:

    Relationship between T1_Organization and T1_Employeee is ok. When clicking on + symbol in the Org table, I seel all associated employees working in that office.

    However, the relationship between Employees and Jobs is not working. I arbitrarily added all the jobs in the table T1_Jobs. There is currently no link between jobs and employees. Also, the jobs listed weren't available in the Org table.

    I'm really not sure how to fix it in the attached database.

    Thank you,
    EEH
    Attached Thumbnails Attached Thumbnails ERD.JPG  
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In my suggestion, Employees would not associate with Organization. Employees would associate with Jobs. Jobs would associate with Organization. Note the field names in my suggestion. There is no OrganizationIDfk field in Employees. Link Jobs to Employees.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    dbqueen is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    1
    insightful topic

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 - so do you recommend a structure similar to my original cascading 3-tiered OrgDesign (Principals to Directorate to Branches, etc.). In this case, a cascading relationship such as OrgIDfk to JobIDpk; JobIDfk to EmpIDpk?

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

Similar Threads

  1. Replies: 14
    Last Post: 08-29-2019, 04:52 PM
  2. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

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