3

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?

FreeDom
  • 33
  • 3
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Nov 13 '19 at 01:53
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS There are many duplicate Q&As re subtypes/inheritance/polymorphism & radio button FKs--multiple FKs to multiple tables--which are a common anti-pattern for it. – philipxy Nov 13 '19 at 01:55

2 Answers2

2

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);
nvogel
  • 24,981
  • 1
  • 44
  • 82
1

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)
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That seems like exactly what I needed. Thanks a bunch :D (my design capabilities aren't totally trash, me happy man) – FreeDom Nov 12 '19 at 21:52