StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONormalization of database for timesheet tool and ensure data integrity
text
Body
copied!<p>I'm creating a timesheet application. I have the following entities (amongst others):</p> <ul> <li><strong>Company</strong></li> <li><strong>Employee</strong> = an employee associated with a company</li> <li><strong>Client</strong> = a client associated with a company</li> </ul> <p>So far I have the following (abbreviated) database setup:</p> <pre><code>Company - id - name Employee - id - companyId (FK to Company.id) - name Client - id - companyId (FK to Company.id) - name </code></pre> <p>Now, I want an employee to be associated with a client, but <strong>only</strong> 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?</p> <p>I thought about creating a many to many table like this:</p> <pre><code>EmployeeClient - employeeId (FK to Employee.id) - companyId \ (combined FK to Client.companyId, Client.id) - clientId / </code></pre> <p>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?</p> <p><strong>UPDATE</strong><br> The scenario is as followed: </p> <ul> <li>A company has multiple employees. Employees will only be linked to one company.</li> <li><p>A company has multiple clients also. Clients will only be linked to one company.</p> <p>(Company is a sandbox, so to speak).</p></li> <li><p>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.</p></li> </ul> <p>In other words:<br> 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). </p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload