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