StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to represent a categorized many-to-many database relationship where categories are specific to one side of the relationship
text
Body
copied!<p>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.</p> <h1>Requirements</h1> <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> <p>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.</p> <h1>Example Diagrams</h1> <p><img src="https://i.imgur.com/IbR0P.png" alt="Image link at end of question"></p> <p>Basically, I have three tables:</p> <h1>Tables</h1> <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> <p>And then a cross-reference table, called Assignments. The two ways I have come up with so far for Assignments are as follows:</p> <h1>Example 1</h1> <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> <p>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.</p> <h1>Example 2</h1> <ul> <li>Assignments <ul> <li>Employee ID</li> <li>Title ID (EmployeeID-TitleID unique)</li> <li><strong>AssignmentID</strong></li> </ul></li> </ul> <p>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.</p> <p>Again, the diagrams are available here: <a href="https://i.imgur.com/IbR0P.png" rel="nofollow noreferrer">http://i.imgur.com/IbR0P.png</a> </p> <p>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.</p> <p>Thanks!</p> <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>

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