I'm working on building a database to manage project assignments, and the one part that's giving me particular trouble is the assignment of job titles to employees with for each project they are working on.Requirements
<ol><li>An Employee can be on multiple Projects at a time</li> <li>A Project has multiple Employees on it</li> <li>A Project has multiple Job Titles</li> <li>An Employee works on a Project under exactly one of the Project's Job Titles</li> <li>Multiple Employees can work under the same Job Title in a Project</li> </ol>
I'm not sure how to represent this using tables; every layout I come up with either makes it possible for an employee to work on a project under a job title from a different project, or they are able to work on the same project under two different job titles.Example Diagrams
<img alt="Image link at end of question" class="b-lazy" data-src="https://i.imgur.com/IbR0P.png" data-original="https://i.imgur.com/IbR0P.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />
Basically, I have three tables:Tables
<ul><li>Projects <ul><li>Project Name (unique)</li> <li><strong>Project ID</strong></li> </ul></li> <li>Employees <ul><li>Employee Name (unique)</li> <li><strong>Employee ID</strong></li> </ul></li> <li>Job Titles <ul><li>Title</li> <li>Project ID (Title-ProjectID unique)</li> <li><strong>Title ID</strong></li> </ul></li> </ul>
And then a cross-reference table, called Assignments. The two ways I have come up with so far for Assignments are as follows:Example 1
<ul><li>Assignments <ul><li>Employee ID</li> <li>Project ID (EmployeeID-ProjectID unique)</li> <li>Title ID (unique)</li> <li><strong>AssignmentID</strong></li> </ul></li> </ul>
This way limits employees to one title per project, but allows them to use a title that doesn't belong to the project in the assignment.Example 2
<ul><li>Assignments <ul><li>Employee ID</li> <li>Title ID (EmployeeID-TitleID unique)</li> <li><strong>AssignmentID</strong></li> </ul></li> </ul>
This way assigns employees to the project through the title, so it is impossible to assign someone to a project with an invalid title. However, this allows an employee to be assigned to the same project under multiple titles.
Again, the diagrams are available here: <a href="https://i.imgur.com/IbR0P.png" rel="nofollow">http://i.imgur.com/IbR0P.png</a>
I know there must be a way to do this cleanly, but I haven't had any real formal training in database design and I can't find anything through my searches except how to make a many-to-many relationship, which isn't exactly what I need help with.
Thanks!<ul><li>EDIT 1 <ul><li>Bolded Primary Key Fields (were underlined in diagram image, but hard to tell since they are the last fields)</li> <li>Added AssignmentID (Primary Key) to Assignments table in the question (was present in designs, forgot to include when creating question & diagram)</li> </ul></li> <li>EDIT 2 <ul><li>Added missing requirement (5)</li> <li>Added headers for examples and requirements</li> </ul></li> <li>EDIT 3 <ul><li>I have 10 rep now, so I can put up the diagram!</li> </ul></li> <li>EDIT 4 <ul><li>Added identifiers for unique keys (individual unique keys identified in diagram, but I don't know how to do compound keys in DIA)</li> </ul></li> </ul>Answer1:
Add an ID field to you
Job Titles table - let's call it
Assignments table has
Employee ID and
This ofcourse means, that to find the Projects for an employee, you need to join through the
Assignments table <strong>and</strong> the
Job Titles table
After discussion in the comments, please disregard the above, I left it only as history.
Now here is the new version: Your
Assignments table needs (as you already considered)
But it also needs a
UNIQUE INDEX(EmployeeID, ProjectID) - this will make it impossible for one employee to be in the <strong>same</strong> project under different titles.
Multiple employees under the same title will still be allowed, as well as multiple titles in <strong>different</strong> projects for one employee.Answer2:
Do it the first way you mention. It doesn't limit a person to one job title, you can create another record with the same Employee ID and Project ID but with a different Title ID, just use a new Assignment ID as the primary key.
You might even want to take Project ID out of the Job Title table.