Database schema design for schedules

Question

Hi!

I have two tables: Companies and Employees.

I also have a relation table Employs which contains the foreign keys company_id, employee_id as a composite primary key. Note: employees can work at multiple companies.

I would like to have another table EmployeeSchedules which simply contains schedules (company_id:integer,employee_id:integer,start_time:datetime, end_time:datetime) for employees working at a company. This will end up being displayed in a calendar widget of some sort.

However, with this design I would have to verify at the application level that the employee actually works at the company before adding a schedule.

I was wondering if there would be a better way to represent this at the database level or just stick with verifying at the application level? For example, if there was a way to link the EmployeeSchedules pkey (company_id,employee_id) with the Employs pkey (company_id, employee_id). Any other design recommendations are welcome!

Answer 1

I would re-define the schema, and add another table:

Person(id, name)
Company(id);
Employee(id, companyId, personId);
Schedules(id, employeeId, startTime, endTime);

That means a an employee record can only be bound to one company. A person can have multiple employee records however. All the "id" columns are unique, and are the primary key of the table. "companyId" refers to the primary key of the company table and so on.