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.
Ask yourself what is the difference between a Department
and a Section
? Are they just organizational objects that may contain either employees or other, smaller organizational objects? Do they have any other differences in terms of their relationships and attributes?
If the answer is no, then you might want to consider replacing both with a single organization table containing an unleveled hierarchy. This can be represented with an involuted (self-referencing) foreign key (i.e. part_of
or reports_to
etc.)
Of course, in a relational database, unleveled hierarchies can be awkward to work with. If you decide to go this route, you should do some research on hierarchical data management techniques, like visitation numbers or adjacency lists.
If you really need to keep Department
and Section
separate because they have markedly different definitions, then you will need to consider implementing a dummy or null Section
for each Department
to handle the situation where there is no analogue in reality. If you go this route, you could consider adding a flag or some other indicator to differentiate between real Sections
and pro-forma Sections
.
Best Answer
This is a great question that shows the importance of breaking down n-ary relationships based on the business rules contained in them and is a good example of the application of forth normal form which deals with multi-value dependencies (MVD).
The following business rules exist:
Following is an ERD in Barker-Ellis notation created using Oracle SQL Developer Data Modeler showing these rules:
The key to the solution is to decompose the ternary relationship into the binary relationships supported by the business rules. Those were the many to many relationships between employees and departments, and between departments and locations. Then, implement the one to many relationship between location and employees that shows that, while an employee may be assigned to many departments, they can be found at only one of that department's many locations.
Note that it is not possible to depict the portion of rule 5 above that states "...which is by definition a location that is an address of a department the employee is assigned to." This is a good example of the limitations of ERDs. Their strength is the ability to visually depict entities and their relationships, and their weakness is they cannot depict more complex business rules beyond relationships. This is why it is vital to investigate and discover all the business rules and not just the referential rules governing associations. Fabian Pascal's paper Business Modeling for Database Design provides excellent detail on all the necessary rules to explore. Ronald Ross has generated a large body of work on this topic as well. Chris Date also wrote a short book on business rules.