I'm creating a timesheet application. I have the following entities (amongst others):
Company
Employee = an employee associated with a company
Client = a client associated with a company
So far I have the following (abbreviated) database setup:
Company
- id
- name
Employee
- id
- companyId (FK to Company.id)
- name
Client
- id
- companyId (FK to Company.id)
- name
Now, I want an employee to be associated with a client, but only if that client is associated with the company the employee works for. How would you guarantee this data integrity on a database level? Or should I just depend on the application to guarantee this data integrity?
I thought about creating a many to many table like this:
EmployeeClient
- employeeId (FK to Employee.id)
- companyId \ (combined FK to Client.companyId, Client.id)
- clientId /
Thus, when I insert a client for an employee along with the employee's company id, the database should prevent this when the client is not associated with the employee's company id. Does this make sense? Because this still doesn't guarantee the employee is associated with the company. How do you deal with these things?
UPDATE
The scenario is as followed:
A company has multiple employees. Employees will only be linked to one company.
A company has multiple clients also. Clients will only be linked to one company.
(Company is a sandbox, so to speak).
An employee of a company can be linked to a client of it's company, but only if the client is part of the company's clientele.
In other words:
The application will allow a company to create/add employees and create/add clients (hence the companyId FK in the Employee and Client tables). Next, the company will be allowed to assign certain clients to certain of it's employees (EmployeeClient table).
Imagine an employee working on projects for a few clients for which s/he can write billable hours, but the employee must not be allowed to write billable hours for clients they are not assigned to by their employer (the company). So, employees will not automatically have access to all their company's clients, but only to those that the company has selected for them. Hopefully this has shed some more light on the matter.
