1

I have a given number of entities, 6 of which are related to one another. However 4 of them (Namely, JobInfo, Child, Spouse and ParentInfo) are linked to the main Person/Employee entity and this employee entity requires the primary key of the company which is CompanyID as the EmployeeID may not be unique. So I have no idea how to design my database now.

From what I know, the only way to do this is to just declare CompanyID as a foreign key in the other 4 attributes. [I'm stumped as Employee can't exist without the Company, essentially making it a weak entity and the other 4 entities cannot exist without the employee entity]

Thanks, its going to be done in mssql in sql management studio

Edit: Forgot to mention that the primary key of Employee will most likely be a composite key of the primary key of Company and Employee

Rye
  • 35
  • 1
  • 5
  • Surely your PK on the Employee entity would have to be a composite of CompanyID & EmployeeID in order to guarantee uniqueness? – DeanOC Mar 20 '18 at 00:34
  • Yea thats the issue now, cause when they become a composite key, it makes the other four entities directly related to the employer when technically speaking, it shouldn't be. – Rye Mar 20 '18 at 00:39
  • 1
    Can you add an identity column to your Employee table, and use that to enforce FK constraints on the other tables? That way you get an immutable ID to use without worrying someone wants to change Company's down the track. – DeanOC Mar 20 '18 at 01:13
  • alright will do – Rye Mar 20 '18 at 01:27
  • Surrogate keys FTW. Generated id column for the employee row breaks that "false dependency" on the company table. The fact the "employee id" isn't unique in your table actually makes it a bit of a misnomer; it's not an id *in this context*. – Will Crawford Mar 20 '18 at 02:01

1 Answers1

0

Err I think I found an answer. I've decided to use the composite key of companyID and EmployeeID for the Employee Table (Since Employee is a weak entity) and use this composite key as a the composite primary key of the other 4 entities as all four of them will be made into weak entities as well.

Rye
  • 35
  • 1
  • 5
  • Just be wary of using "real" values as relationship keys. What happens if someone decides to change to another Company? Will this mean you need to change the PK for that employee, and cascade the change to all related entities? – DeanOC Mar 20 '18 at 01:32
  • From what I know, if someone joins another company, a whole new record will be created for them since the original company would want to store the information of past employees as well. – Rye Mar 20 '18 at 02:19