Person -> Details database structure

Question

I have a Domain model:
Employee: Id, FirstName, LastName, Sex, BirthDate.
Office: Employee, WorkStation, OfficeName, etc..
Contacts: Employee, MobilePhone, EMail, etc...

But I`m not sure about my current database structure. How is right: Employees table has a PK EmployeeID, and the Offices and Contacts tables has their own IDs and reference to Employees table, OR Employees table has its EmployeeID and also keeps references to Offices and Contscts by having OfficeID and ContactID?

Answer 1

To be in a reasonable normal form, your employees should refer to the office.

Assuming the relationship of contacts is employees have a set of contacts and no other employee shares those contacts, the proper relationship should be contacts refer to the employee.

Employee: empid, officeid

Office: officeid

Contacts: empid, contactid

Answer 2

If data in Office and Contact table just enhance information about Employee, I would use EmployeeID as primary key and foreign key to Employee. That models 1 to 0..1 relation.

Employee: EmployeeID as primary key

Office and Contacts: EmployeeID as primary key and foreign key to Employee

Answer 3

Your model is not explicit about the relationships between Employees, Offices, and Contacts. Specifically, are these relationships one-to-one, one-to-many, or many-to-many? Once you've catalogued the relationships you want modeled, the foreign key structure of your database will become clear.