Entity relationship problem

Question

I have 4 tables related like this (it's an example):

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company 

Classification:
ID
Name
Code
ID_Company

Workers:
Id 
Name
Code
ID_Classification
ID_Department

Suppose that I have a classification with id = 20, id_company = 1. And a department that has id_company = 2 (that represents another company).

This will allow the creation of a worker that is from two companies because the classification and department are linked to the company separately. I don't want that to happen, so I think I have a problem with my relationships and I don't know how to solve it.

Answer 1

Your issue stems from the fact that there is an entity type missing from your model. Consider the following ERD:

Note that I've added an intersection entity type between DEPARTMENT and CLASSIFICATION. This new entity type: POSITION provides the information which is implicit in your model, that a particular department has a give set of jobs of various classifications.

Adding POSITION to your model as an explicit entity has a few advantages.

1. It avoids the problem that you are concerned about with a WORKER potentially being assigned to departments and classifications in different companies.
2. It gives you a locus for other predicates that might be applicable to a position, such as salary grade, etc.
3. It allows you to record the fact that a position exists, even if there are no WORKERs currently in the position, which is quite possibly useful information.

Note that to avoid the problem of a position being defined for a department and a classification that are in different companies, I've expanded the keys of both DEPARTMENT and CLASSIFICATION, which is good for the reasons you can read about at length in Todd Everett's answer.

BEWARE The model above presumes a simplification. Specifically, it assumes that each position is recorded only once. This may or may not be suitable to your business rules. If you need multiple POSITION records for the same department and classification within a company, then you can introduce a surrogate key in POSITION.

Answer 2

I don't think you have a problem with the relationships. I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa. A good way to understand this is to visualize the schema using an ER Diagramming tool. I will use the Oracle Data Modeler tool which is a free download.

ER Diagram

As it stands, you could have 2 companies - say IBM and Microsoft. IBM can have a Software Development department, and Microsoft can have a Desktop Software department. IBM can have a Software Engineer classification, and Microsoft can have a Software Developer classification. Now, because you have a surrogate key for Department and Classification, the fact that Software Development is an IBM department and Desktop Software is a Microsoft department is lost for future child relationships. This is also the case with Classification. Therefore it is easy to accidentally assign Harlan Mills, who is an IBM employee in the Software Development department, a classification of Software Developer which is a Microsoft classification! Likewise, the worker could be given the right classification and wrong department! Here is a diagram showing the first example:

The 1 Ids represent IBM, and the 2 Ids represent Microsoft. I've highlighted in red the scenario where Harlan Mills and Bill Gates are assigned to the wrong departments, which is visualized by the 10 department Id associated to the 200 classification Id and vice versa.

Options to Resolve

So what are the options to prevent his from happening? There are two immediate options. The first is to realize that by using a surrogate key for every table this issue exists and introduce additional programming to verify it doesn't occur. This could be done in the application, but if inserts and updates can occur outside of the application then incorrect associations can still occur. A better approach would be to create a trigger that fires on insert and update of an employee to make sure that the assigned department is of the same company as the assigned classification, and if not fail the insert or update.

The second option is to not use surrogate keys for every table. Instead, use the surrogate keys only for the Company table, which is fundamental and has no parents, and then create identifying relationships to the Department and Classification child tables. The Department and Classification tables now have a PK of the Company Id plus a Sequence Number or Name to distinguish them. Then, the relationships from Department and Classification to Worker also become identifying and thus the PK of Worker becomes the Company Id, plus the Department Number (I'm using a sequence number in this example), plus the Classification Number. The result is there is only one Company Id in the Worker table. It is now impossible to assign a Worker to a Department in one Company and to a Classification in another Company.

Why is this impossible? It is impossible because the schema implements referential integrity between Worker and Department and Classification. If an attempt is made to insert a Worker for a Department in one Company and and a Classification of another, the combination that does not exist in the corresponding parent table will trigger a referential integrity violation and the insert will not work.

Here is an updated diagram of an implementation of the second option:

Preferred Option

Of the two options, I absolutely prefer the second - using the identifying relationships and the cascading keys - for two reasons. First, this option achieves the desired rule with no additional programming. Developing a trigger is not trivial. It must be coded, tested, and maintained. Ensuring the trigger logic is optimal so as not to impact performance is also not trivial. The book Applied Mathematics for Database Professionals provides a lot of detail on the complexity of such a solution. Second, the rules imply that a Department and a Classification can not exist outside the context of the Company, and so the schema now more accurately reflects the real world.

This is a great question because it shows exactly why simply assuming every table requires a surrogate key is a bad idea. Fabian Pascal has an excellent blog post on just this topic showing that not only can a surrogate key be a bad idea from a data integrity standpoint it can also result in making some retrievals slower at the physical level precisely because joins are required that, had keys been properly cascaded, would be unnecessary. Another interesting topic this question reveals is that a database cannot ensure that all data inserted into it is accurate with respect to the real world. Instead, it can only ensure that the data inserted into it is consistent with the rules declared to it. In this case we can do the best possible by using the cascading key approach to ensure the DBMS can keep the data consistent with respect to the rule that a Worker of a given Company needs to be assigned a Classification and a Department of that same Company. But, if in the real world Microsoft has a department called Desktop Software but the user of the database asserts the department is instead Software Development the DBMS can do nothing but assume it has been given a true fact.

Answer 3

The way I understand the question is, ID_Classification field of 'Workers' table should only allow the classifications defined for respective worker's company. Hence, validating(by attaching a RULE or through TRIGGERS) the information inserted/updated into Workers.ID_Classification field is adequate to address this requirement.

Answer 4

From my readings, I still not understand what is this Classification and why does it need to have the ID_Company. If it's like a position as someone mentioned here, I'd think a static table to contain all positions would be better.

If you are doing this in order to easily find a classification/position in a company, please add a simple query/view to connect classification-workers-departments and retrieve the company id of the classification.

nowadays, there are smarter views or technologies such as materialized views and join indexes, so if your issue is performance of the query, use them.