Database Design – Resolving Many-to-Many Relationship in ERD

Good. The essentials are there. A first rough list of entities is to take the nouns of the story - office, employee, project, clients, assignment, skill. Each table has a unique, sequential, meaningless key. Columns are given descriptive names. Here are the suggestions by entity.

office> The manager is an employee and should have a separate foreign key relationship with manager_employee_id. There is a many-to-many with employee. Avoid M-to-M relationships. In this case, an office has many employees. But an employee has one home office. I suggest naming fields uniquely with a common suffix describing the domain, office_address, instead of address.

employee> The job_id should be a foreign key to job instead of title. The office_id should be a foreign key to office instead of location. The column names should be hire_date instead of the start_date to avoid confusion with the project start_date. Use employee_address instead of address based on the domain suffix rule.

project> The project manager is a separate foreign key to employee pm_employee_id. What is the business difference between estimated_cost and quoted_cost? Make sure there is a foreign key field to match every relationship line. Client_id should be the foreign key. Office_id should be a foreign key.

assignment> Good, this is an intersecting table between project and employee. I suggest renaming the date to assigned_date to keep the suffix consistent. Notice that the hours_worked are in this table with the assigned_date. The assigned_date happens once. But the hours worked happen daily.

daily_assignment> Maybe there should be a separate child table called daily_assignment with its own primary key, a foreign key to assignment, and the worked_date and hours_worked as fields. This tracks each item of data uniquely at its own level.

client> I suggest renaming the address to client_address.

There are ways to make it more complicated. But I think this model works well. It should give you flexibility to make business changes, yet still be simple enough to code.

This is a case of what's called "generalization/specialization" in ER lingo. It's really the same thing as what object models call "superclass/subclass" as you have done. There are two separable issues. The first is how do you want to draw the diagram, and the second is how do you want to design the tables.

As far as drawing the diagram goes, I would draw it the same way you have, with one exception. The lines leading out of Managers and Drivers would not go separately to Employees. Instead, they would go to something I'll call a gen/spec box. The following sample shows one way to depict a gen/spec case. Then a single line connects the gen/spec box with the Employees table. It all depends on which way seems simpler and clearer to you and to others who look at the diagram.

As far as tables go, you could have three tables, just as you have shown. This is sometimes called "class table inheritance". There is a technique you could benefit from, called "shared primary key". In this technique, the manager table and the driver table do not have an id field of their own. Instead, there is a copy of Employee_id in each subclass table, and employee_id is declared to be both the primary key of this table and also a foreign key that references Employees.

Using shared primary key buys you a few things and it costs you something.

It buys you enforcement of the 1:1 limit on manager-employee or driver-employee. It may end up speeding the resulting joins. And it allows a fourth table that uses employee-id as a foreign key to be joined directly to Drivers or Managers. The irrelevant employees drop out of the join.

What it costs you is that you have to obtain and use a copy of the right employee_id every time you go to insert a driver or a manager.

You could also just pack driver attributes and manager attributes into the employee table, leaving irrelevant fields NULL. That results in fewer joins, but it does have a downside. It makes the employee table slightly bigger and slower. And if you use nullable fields in WHERE clauses, you'll have to learn how SQL does three valued logic (True, False, and Unknown). For some people, this is awfully difficult to grasp. I try to avoid three valued logic myself.