0

I am designing a datamodel for a new project.

One of the requirements specifies that some objects can point either a person or a company.

What is the smartest way to achieve that?

I have thought about a table link "actor" like this (drawn with the excellent yUML.me BTW) :

enter image description here

In the actor table, according to actor_type, person_id or company_id is a foreign key on its corresponding table or is NULL. This way, when one_table wants to retrieve details about the actor, I start by checking the actor_type field and retrieve either person_id or company_id.

It is working, but I am looking for a better design. Here is the link for editing the diagram

2

It seems, that you don't need the actor_type table at all, since there is just those two types. As the column and table names are already human readable, even more actor types could be added the same way.

The actor type is neither necessary for ruling out which one this actor is, since the other(s) can be left NULL. Only one being NOT NULL can also be made mandatory with constrains, e.g.

ALTER TABLE actor
ADD CONSTRAINT OnlyOneActor
CHECK (
   (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
   = 1
)

However, this won't actually work as supposed in MySQL: Reference Manual states that

The CHECK clause is parsed but ignored by all storage engines.

You can achieve the same functionality in MySQL by using a TRIGGER instead, e.g.

DELIMITER //
CREATE TRIGGER OnlyOneActor BEFORE INSERT ON actor
FOR EACH ROW BEGIN
  IF (
     (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
     != 1) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Only one type of actor allowed.';
  END IF;
END//
DELIMITER ;
3
  • Thanks for explaining the way to create such complex rules. I will study the fact to not use actor_type table.
    – sdespont
    Apr 4 '15 at 10:34
  • 1
    MySQL does not support check constraints. See: dba.stackexchange.com/questions/9662/… Apr 5 '15 at 3:38
  • MySQL supports CHECK, but any of its database engines doesn't actually check constraints. Even InnoDB only checks foreign constrains. Answer updated accordingly. Apr 5 '15 at 7:02

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.