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