40084

How to represent a categorized many-to-many database relationship where categories are specific to o

Question:

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 JobTitleID

Now your Assignments table has Employee ID and JobTitleID.

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

<strong>EDIT</strong>

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)

<ul><li>Employee ID</li> <li>Project ID</li> <li>Title ID</li> <li>AssignmentID</li> </ul>

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.

Recommend

  • Displaying chart data from a database
  • how to implement RoR many-to-many relationship?`
  • Join 3 tables with Count
  • How does Dispose work with Entity Framework
  • Is it possible to set multiple variables with one ternary operator?
  • ORA-02298 Parent Keys Not Found?
  • PHP throws 'Allowed memory exhausted' errors while migrating data in Drupal
  • SUM() data in a column based on another column data
  • Why gcc does not produce type mismatch warning for int and char?
  • Get the computer user name in a web application
  • What's a better way to swap two argument values?
  • Module parameter permission
  • opengl: adding higher resolution mipmaps to a texture
  • Excel not inserting leading zero
  • how to update a column in a table with unique values, but not all the rows, not incremented [duplica
  • Can't zip RDDs with unequal numbers of partitions
  • DotNetOpenAuth - how to uniquely identify Google users?
  • HABTM associations in Rails : collecting and counting the categories of a model's children
  • How do `pass` and `listen` work in WriterT?
  • How do I refer to a client_deploy.wsdd file that's in WEB-INF?
  • Javascript/Jquery runs fast in desktop browsers, but slow in mobile/smartphone browsers…should I spl
  • c++ regex_replace not doing intended substitution
  • using System.Speech.Synthesis with Windows10 universal app (XAML-C#)
  • GAE: Way to get reference to an HttpSession from its ID?
  • Spring boot 2.0.0.M4 required a bean named 'entityManagerFactory' that could not be found
  • What is the purpose of TaskExecutor in spring?
  • Tamper-proof configuration files in .NET?
  • Google Custom Search with transparent background
  • Submit form in a displaytag pagination
  • Javascript + PHP Encryption with pidCrypt
  • Delete MySQLi record without showing the id in the URL
  • Why winpcap requires both .lib and .dll to run?
  • WOWZA + RTMP + HTML5 Playback?
  • Unanticipated behavior
  • Comma separated Values
  • How to set the response of a form post action to a iframe source?
  • Trying to get generic when generic is not available
  • How does Linux kernel interrupt the application?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • unknown Exception android