CODEWITHSUNDEEP
sqldatabase-design
FreeDom
FreeDom

Reputation: 33

Is there a way to have a ternary relationship where one of the three entities is only associated with 1 of the two other entities at a time?

So my current problem is mostly modeling related because I haven't tried applying it in SQL yet.

What I currently have is 3 entities - Director, Actor and Film - all related with a relationship - DIRECT (1:1:N) partial:partial:total.

The idea is that each film can only have 1 Director, however actors can be directors too, so the dilema was finding a way to have a film be directed by either an Actor or Director but not allowing them to be Directed by both at the same time, however the way it's currently modeled I can't stop this from happening.

Is there some SQL command that I can use when turning this into a proper database or do I have to take another modeling approach?

Upvotes: 3

Views: 286

Answers (2)

nvogel
nvogel

Reputation: 25534

Create a new table (artist) that represents all the actors and directors in one place. That way you only need one column in the film table for the director:

CREATE TABLE artist (artistid INT NOT NULL PRIMARY KEY);

ALTER TABLE actor ADD FOREIGN KEY (actorid) REFERENCES artist (artistid);
ALTER TABLE director ADD FOREIGN KEY (directorid) REFERENCES artist (artistid);
ALTER TABLE film ADD directorid INT NOT NULL REFERENCES artist (artistid);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can use foreign key constraints and a check constraint. Here is a scaled down version of a table and how this is set up:

create table directedBy (
    filmId int not null references films(filmId),
    actorId int references actors(actorId),
    directorId int references directors(directorId),
    check (actorId is null or directorId is null)
);

Upvotes: 1

Related Questions