Database : what design to point on two possibilities? 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) :



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
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 ;