Normalization of database for timesheet tool and ensure data integrity

Question

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.

Answer 1

If you want to do it from the database level then I would put the logic in a stored procedure. The stored proc code will then associate the two if applicable but this means that (given you put the foreign key to the employee in the client table) a client is only associated with one employee. Is this what you want?

Also take note though that an employee in your table is indirectly associated with all such clients via its company association. If all employees are automatically associated with all new clients of their company then perhaps you just want to write a query that checks for this.

Answer 2

(This is not an answer, but it didn't really fit in as a question comment.)

The data presented for your design question begs a number of questions:

• Are employees to be associated with companies and clients? Or...
• Are employees only associated with clients, and (thus) the company associated with that client?
• If employess and clients are associated with companies, is an employee thus associated with all employees of that company, or must you pick and choose?

Update

As far as data modelling is concerned, it seems like all you need to do is expand the foreign key in EmployeeClient into Employee like so:

EmployeeClient
 - companyId
 - employeeId
 - clientId

Compound primary key on all three columns.

Foreign key on (companyId, clientId) into Client
Foreign key on (companyId, employeeId) into Employee

Thus, all relations defined in EmployeeClient require both Client and Employee to share the same clientId.