3

Let's say we have a Person relation, and with that Employee which IS-A Person, Manager, which ISA Person as well.

What is the correct way to ensure that a Manager isn't an Employee and vice versa ?

I didn't know what to look for when asking the question, there most likely already exists an answer to that question.

Person (PK:personId, name, ...);
Manager(PK:#personId); -- A manager ISA person
Employee(PK:#personId); -- An employee ISA person

INSERT INTO Person (1,'Peter',...);
INSERT INTO Manager (1); -- OK
INSERT INTO Employee(1); -- Nope, error.

Person cannot be at the same time an employee and a manager.

Maybe with another example.

Shape (shapeId);
Rectangle(shapeId, width, length);
Circle(shapeId, radius);

Circle is a shape, Rectangle is a shape, how do I ensure Rectangle is not a Circle (and vice versa)?

Hence my question, What is the correct way to enforce unique "IS-A" relationship?

  • An employee is not a person. A person can play the role of employee. They can play the role of a customer, at the same time, too. – Neil McGuigan Aug 21 '18 at 20:36
  • @NeilMcGuigan generaly, yes. But rarely in B2B. I just encounter this very problem quite often and wanted to formalize it logically. For instance, a transporter can be either a truck or a container, but not both, a user can be an web-admin xor a buisness-admin xor a customer. Or many tiny inconsequential examples, that are implemented on the software layer, that I would like to see enforced. Cf edits in my posts. – Pierre-Antoine Guillaume Aug 21 '18 at 20:44
3

Relation Application that refer to the Person and Position relations should have unique personID attribute and should have the single attribute positionID that can have one and only one value at once.

1

Confusion

You have two separate questions here which prevent this from being answered

  • You have one question about relationships (IS-A).
  • You have one question about constraints "manager isn't an employee".

One of them is about modeling, the other integrity. That makes it difficult to answer

Modeling

Any time you see IS-A with a (1:1) in the scope of an RDBMS, you should think simple foreign keys.

CREATE TABLE person (
   person_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   -- stuff
);

CREATE TABLE manager (
   manager_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   person_id   int REFERENCES person,
   -- stuff
);

CREATE TABLE employee (
   manager_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   person_id   int REFERENCES person
   -- stuff
);

If you have multiple inheritance (M2M), you'd use a linking table.

Integrity

"Manager isn't an Employee and vice versa"

This can only be done if either

  • The relationship is stored in one table
  • The database implement CHECK constraints which support queries
  • You use a TRIGGER

That's your only option. If the relationship is stored in one table.

CREATE TABLE relationships (
  id_person  REFERENCES person,
  id_manager REFERENCES manager,
  UNIQUE ( greatest(id_person,id_manager), least(id_person,id_manager) )
);

And that should work.

Other Issues

These very simple cases often face bigger problems in the real world, for instance n-hierarchy relationships. What if the manager has a manager. That process of a separate table for each can get really absurd fast.

You could just do this,

CREATE TABLE persons (
  id_person     int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  id_superior   REFERENCES persons
  ; stuff
);

But in this case there is no easy way to ensure id_person and id_superior do not create cyclic relationships in the graph, so again you have to get more clever...

And that's why this kind of question requires more information.

  • What does this line do ? : CREATE TABLE relationships ( id_person REFERENCES person, id_manager REFERENCES manager, UNIQUE ( greatest(id_person,id_manager), least(id_person,id_manager) ) ); – Pierre-Antoine Guillaume Aug 19 '18 at 12:47
  • Stores the relationships. You drop the employee table and store them like that instead. The UNIQUE condition ensures no bob is the manager of joe, and joe the manager of bob. – Evan Carroll Aug 19 '18 at 15:50
  • 1
    Ok so that's what that unique does. But then, it does not answer my question, on how can I make sure to have a single "type" for a person. – Pierre-Antoine Guillaume Aug 20 '18 at 7:31
  • @PierreAntoineGuillaume if you want to enforce static type, then use an enum which adds two bytes on the row (almost nothing). But be careful in your wording because types and relationships are different notions in every aspect. Something is 1:1 it is said it "is a" but it can be "is a" to more than one thing. – Evan Carroll Aug 22 '18 at 17:47
  • So in this case, type is a proper name ? – Pierre-Antoine Guillaume Aug 22 '18 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.