Results 1 to 5 of 5
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Junction table... potentially need "dummy" record to add 3rd key element??

    Experts:



    I need some recommendations with managing "relationships" in an existing database.

    Attached (**conceputal**) database includes three tables (Organization, Billets, Staff Members). Via a form (not included) all auto IDs are "managed" in the junction table "OBS".

    In preparation for a redesign this database is working well. I CANNOT want to completely change the existing structure. "That train has left the station" so to speak.

    Here's the general concept of the database:
    1. An organization has many sub-organizations (Division ABC, Division KLM, Division XYZ).
    2. Within each suborganization, there will be multiple billets/jobs (accountant, data analyst, vice president, etc.).
    3. Naturally, the organization has staff members (employees).

    As part of the redesign of actual database, the following occurred:
    - Cleaned up/normalized all sub-orgs
    - Cleaned up/normalized all billets/jobs
    - Currently in process of normalizing all staff members

    Process:
    - Using a form (again not included in this demo), a billet AND a staff member will be assigned to the organization.
    - This process created a 3-part key in the junction table (OrganizationIDfk; BilletIDfk; StaffMemberIDfk).
    - However, as part of the staff members table cleanup, we realize that some billets/positions may be vacant.
    - At the same time, the billet/position is still part of the organization. Yet, w/o a StaffMemberID I cannot create the relationship between the 3 tables through the junction table.

    My question(s):
    1. Based on the current construct, how can I assign a **billet to an organization** without adding a staff member? In order words, it's a valid relationship (billet to org); however, an employee may left the organization so the billet is vacant for the time being?
    2. Is creating a "Dummy" employee (e.g., StaffMember = [VACANT] with StaffMemberIDpk = 1) a possible work-around? So, basically, I still can create the 3-way relationship by merely adding employee [VACANT]?
    3. Or does the latter cause any potential issue as I **will** assign "Mr. [VACANT]" multiple times to different billets/jobs?

    Any thoughts on this topic will be appreciated!! Again, a complete redesign is out of the question but creating a work-around (VACANT employee) is still doable. I just need to know if there are other possible options out there in the event assigning a dummy [VACANT employee] multiple times could cause issue down the road. Thoughts?

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I hate to say it but I think you have this design a little off.

    Let's make a fundamental assumption, a current employee must have a job ?
    On that basis you could move the staff member join to the job (billet) table and remove it from the junction table.

    That solves your current problem and is I think a more sound layout.
    Your querying on open positions becomes simple - billets with a missing EmpID.

    Don't be fixated on not altering things, if the design isn't correct you are storing trouble for later.

    Using Mr Vacant seems like a kludge to me, and also I would possibly want some method of storing who did what job when, which current design doesn't cater for that as far as I can see.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in addition to minty's comments, you have set your relationships to cascade deletes. Do you really want to delete a billet associated with an organisation if you delete the employee?

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty -- the design attached was proposed by another Expert in this forum... ultimately, it works well.

    Quick summary:
    - The organization/sub-organization has divisions...
    - Each division/org has billets... billets must be linked to the org
    - Now, not each billet is filled. So, I may have 10 billets but only 9 employees, leaving 1 position vacant.
    - If the billet goes away (e.g., lack of funding), the associated employee may transfer into another position or leave the organization.
    - So, there will never be an "orphan" employee (one w/o a billet).

    If you have a different design, I'd welcome a sample database (no records... just structure). I will then check it out.

    W/r to the vacant position, the organization (HR office) knows that that have a certain number of vacancies. Thus, linking e.g., "[Vacant]" (part of employee table) to it appears to work well for n number of positions.

    //

    Wrt to the cascading deletes, I just finished loading some data today. However, deleting an employee will not delete the position nor the organization. I should only remove the record in the junction table.

    Again, thanks for chiming in... looking forward to see if you could provide an example db.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    However, deleting an employee will not delete the position nor the organization. I should only remove the record in the junction table.
    didn't say it would - it will delete the record in the junction table.

    So you have organisation X, billet of CEO and that is filled by Joe, all mapped out in the junction table

    Joe leaves and you delete his record. That will delete the record in the junction table so now organisation X does not have a CEO billet - you will need to recreate the junction table record so that you can see it has a CEO vacancy.

    So to get round that, perhaps you create the CEO record with a vacancy before you delete Joe's record - then you forget to delete him from the system so now you have two CEO billets for that organisation.

    Either way you are deleting records - which is a) generally considered bad practice, b) will result in bloat and a long term slowing down of your app as the indexing becomes fragmented and c) cause confusion when someone forgets to do something - or does something by accident

    As you have been advised before and Minty has stated again, you should include start and end dates in your record structures.

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. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  3. How to create a "dummy" field name
    By accessmatt in forum Access
    Replies: 1
    Last Post: 04-28-2015, 11:20 AM
  4. Replies: 4
    Last Post: 02-18-2015, 11:28 AM
  5. Replies: 3
    Last Post: 04-10-2010, 10:22 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