CODEWITHSUNDEEP
sqlrelational-databaseentity-relationship
BinaryDog
BinaryDog

Reputation: 33

How do I enforce a total participation constraint on 1:N relationship in a relational database?

Employee_Department_ER

In this example, entity Department is managed by many employees and each Department must participate in such relationship (double lines indicate total participation). Entity Employee can manage one Department and each Employee may or may not participate in such relationship.

I'm wondering what is the correct way to map this into a relational schema in a database. I've done some search on Google and other posts but their answers still confuse me. One approach is to use

Employee(<attrs_for_employee>, department_id) // department_id is a foreign key that refers to Department's primary key
Department(<attrs_for_department>)

This approach clearly shows the 1:N relationship, but I don't see any total participation in the Department (Department might or might not be managed by an Employee).

If we extends Department instead,

Department(<attrs_for_department>, employee_id)
Employee(<attrs_for_employee>

This approach shows the total participation in the Department but loses the 1:N relationship.

Is there a correct way to show both the 1:N and total participation in relational schema? Is it necessary to create a new schema, Employee_Department that merges both of the primary keys like in a M:N relationship?

Upvotes: 2

Views: 2276

Answers (1)

James Madison
James Madison

Reputation: 943

Use a database trigger. The trick here is that "a department must be managed by one or more employees" can't be directly represented in the model itself. It has to be in logic. The best logic is in a trigger. The exact code for your vendor product will vary, but the logic is this:

  • Put a BEFORE INSERT trigger on DEPARTMENT.

  • In that trigger, get the EMPLOYEE_ID of the incoming INSERT.

  • Go find that in the EMPLOYEE table.

  • If it is there, INSERT.

  • If it is not, throw a message saying that one EMPLOYEE must be added first, and that such an employee must be used in the record to be inserted in DEPARTMENT.

  • Put a BEFORE DELETE trigger on EMPLOYEE. This is to ensure you don't later orphan the DEPARTMENT.

  • In that trigger, ensure that DEPARTEMENT has at least two EMPLOYEEs, so that the one you're about to delete is not the last one.

  • If two or more, let the DELETE pass.

  • If just one, throw an error saying you can't DELETE the last EMPLOYEE.

Categorically, there are many rules that must be enforced in code that cannot be enforced in the model. Triggers are the way to go.

If you're on a platform that doesn't do triggers well or at all, you have to write this either in stored procedures run on a schedule, or in stand-alone code (e.g. Python, Java, etc.) and run on a schedule. Triggers are magical for data rules!

Upvotes: 2

Related Questions