3

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

  1. An Employee can be on multiple Projects at a time
  2. A Project has multiple Employees on it
  3. A Project has multiple Job Titles
  4. An Employee works on a Project under exactly one of the Project's Job Titles
  5. Multiple Employees can work under the same Job Title in a Project

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

Image link at end of question

Basically, I have three tables:

Tables

  • Projects
    • Project Name (unique)
    • Project ID
  • Employees
    • Employee Name (unique)
    • Employee ID
  • Job Titles
    • Title
    • Project ID (Title-ProjectID unique)
    • Title ID

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

  • Assignments
    • Employee ID
    • Project ID (EmployeeID-ProjectID unique)
    • Title ID (unique)
    • AssignmentID

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

  • Assignments
    • Employee ID
    • Title ID (EmployeeID-TitleID unique)
    • AssignmentID

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: https://i.stack.imgur.com/U8C16.png

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!

  • EDIT 1
    • Bolded Primary Key Fields (were underlined in diagram image, but hard to tell since they are the last fields)
    • Added AssignmentID (Primary Key) to Assignments table in the question (was present in designs, forgot to include when creating question & diagram)
  • EDIT 2
    • Added missing requirement (5)
    • Added headers for examples and requirements
  • EDIT 3
    • I have 10 rep now, so I can put up the diagram!
  • EDIT 4
    • Added identifiers for unique keys (individual unique keys identified in diagram, but I don't know how to do compound keys in DIA)
PeaBucket
  • 142
  • 1
  • 1
  • 9

2 Answers2

2

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 and the Job Titles table

EDIT

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)

  • Employee ID
  • Project ID
  • Title ID
  • AssignmentID

But it also needs a UNIQUE INDEX(EmployeeID, ProjectID) - this will make it impossible for one employee to be in the same project under different titles.

Multiple employees under the same title will still be allowed, as well as multiple titles in different projects for one employee.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • It has an ID, Title ID, it's just at the bottom. – SpacedMonkey Jun 27 '12 at 14:37
  • @SpacedMonkey I understood this to be a FL into a (not shown) table of **possible** job titles. If it were the PK for the table `Job Titles`, then it would not *"allow[s] them to use a title that doesn't belong to the project in the assignment."* as stated in the OQ. – Eugen Rieck Jun 27 '12 at 14:41
  • This is one of the provided solutions I have up, I just named it (the job title id) TitleID instead of JobTitleID (for some reason). It would be much easier to see if the website would let me post the diagrams onto the page, but this is my first question... In the second Assignments example, assignments link an employee to a title, which in turn links to the project. the problem with it is that an employee can link to multiple job titles of the same project. EDIT: I forgot the assignment PKs and didn't label the table PKs as such. fixing now – PeaBucket Jun 27 '12 at 14:42
  • You can avoid this by dropping the `Assignments` table and adding a `Employee ID` field to the `Job Titles` table - this makes it a 1:1 relationship, with every job title assigned to exactly one employee. – Eugen Rieck Jun 27 '12 at 14:46
  • Ah, I did a bad job with describing the requirements; Multiple employees can work under the same job title. I'll update the question with that. – PeaBucket Jun 27 '12 at 14:50
  • Oh my god, how did I not realize that sooner? I've been using composite unique keys all over this database... This is the last time I draw out a design on paper! Thanks! – PeaBucket Jun 27 '12 at 15:06
  • Nevermind.... Disregard the accept, it's still the same problem. The problem with example one wasn't an employee being on the same project twice (employee-project was implied unique, will have to put in more formatting), but with having an employee be on a project under another project's job title. – PeaBucket Jun 27 '12 at 15:20
  • which is easily cured: 1. Make `Title ID` be a `VARCHAR` automatically generated by Triggers having the form `ProjectID-TitleID`, both numbers zero-padded to 11 characters (With `ON UPDATE CASCADE`). 2. Drop `Project ID` from `assignment` (including the unique key), but keep `Title ID`. 3. Create a `UNIQUE KEY(EmployeID, TitleID(11))` – Eugen Rieck Jun 27 '12 at 15:31
0

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.

SpacedMonkey
  • 2,725
  • 1
  • 16
  • 17
  • the problem isn't restricting employees to one job title per project (that's the point, actually) but making sure they don't use a title from a different project. As far as removing ProjectID from the assignment, that's what is done in the second example. the problem with that is it allows an employee to be on the same project under many of its job titles. I updated the questions with headers for the examples; maybe someday I'll figure out how to format a question to make it understandable D: – PeaBucket Jun 27 '12 at 15:00