0

I'm trying to define a subtype but I have no idea how, I've googled it but I didn't find anything concrete.

What I'm trying to do is build a netflix-like database which has movies and series.

So I have my "supertable" called "images" (didn't know how to word it better) which has the common data like the name, duration, etc... and then I want to define the subtype which is either a movie or a series.

Why? Because if it's a series I also need to keep track of the amount of seasons and episodes, and that's not necessary for a movie.

How can I do this? Any help is appreciated

Thanks in advance!

Edit: My create tables statement looks like this:

create table images(
imageID varchar(3),
name varchar(30) not null,
duration varchar(3) not null,
description varchar(255) not null,
IMDB varchar(3),
rating varchar(3),
castID varchar(3),
directorID varchar(3),
primary key (beeldID)
);

create table movies(
imageID varchar(3),
primary key (beeldID),
foreign key (beeldID) references beelden(beeldID)
);

create table series(
imageID varchar(3),
amountSeasons varchar(2) not null,
amountEpisodes varchar(2) not null,
primary key (beeldID),
foreign key (beeldID) references beelden(beeldID)
);

Would this work if I for example need to select all movies and compare the imageID from images and imageID from movies with eachother to be the same?

Bram-N
  • 426
  • 4
  • 15
  • The thing to search for would be "Polymorphic relationships". Bill Karwin has a few excellent answers on the matter [here is one](http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables/441111#441111) and I'll link you to a few others as I turn them up. – Michael Berkowski Dec 23 '15 at 15:00
  • Oh convenient - [he made a list of them](http://stackoverflow.com/a/3383320/541091) In particular, you might find [this one useful](http://stackoverflow.com/a/2003042/541091) – Michael Berkowski Dec 23 '15 at 15:02

0 Answers0