CODEWITHSUNDEEP
database-designdata-modeling
alkersan
alkersan

Reputation: 3447

Person -> Details database structure

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?

Upvotes: 0

Views: 513

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 0

LukLed
LukLed

Reputation: 31842

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

Upvotes: 1

groundhog
groundhog

Reputation: 4780

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

Upvotes: 1

Related Questions