CODEWITHSUNDEEP
mysqlsql
K. Ayoub
K. Ayoub

Reputation: 426

MySQL schema design: one table with type column VS multiple tables

I'm making a website about movies, i want to make a relationship between a movie and the cast (director, writers and actors), there's two possibilities of how i can achieve that, the first is to create a table of each cast: 

CREATE TABLE director(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));  
CREATE TABLE writer(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));  
CREATE TABLE actor(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));

And make a Many To Many relationship between each of this tables and movie table.
The second possibility is to create one table for the cast with a type column which can refer to director, writer or actor and make a Many To Many relationship between that table and movie table.. 

CREATE TABLE cast(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), type varchar(10));

Note: for the actor, the auto-generate table will have some additional columns: character_name, role...
So which possibility is better for this situation ?

Upvotes: 6

Views: 3024

Answers (3)

sticky bit
sticky bit

Reputation: 37472

Both approaches neglect, that an actor or director or writer or ... (usually) is a person. That is the actual entity. The role (actor, writer, ...) a person had in a movie, is actually a relationship attribute.

In both approaches there'd be redundancies. If someone had multiple roles in a movie, e.g. directed and acted (think of Hitchcock's cameos), there'd be two records for that person. Those were distributed across two tables in the first and in one table in the second approach. The attributes of a person like name, etc. would be stored twice and could contradict each other or needed to be changed in both places, if they change, etc..

So I suggest a third approach: Have a table for people storing all the attributes a person can have (name, ...). And have a table linking people to movies also indicating the role (actor, director, ...) a person had in a movie.

It's probably also not a bad idea to have a table for the roles.

E.g.:

CREATE TABLE people
             (id integer AUTO_INCREMENT,
              name varchar(50),
              PRIMARY KEY (id));

CREATE TABLE roles
             (id integer AUTO_INCREMENT,
              name varchar(10),
              PRIMARY KEY (id));

CREATE TABLE movies_people
             (movie integer,
              person integer,
              role integer,
              PRIMARY KEY (movie,
                           person,
                           role),
              FOREIGN KEY (movie)
                          REFERENCES movies
                                     (id)
                          ON DELETE CASCADE,
              FOREIGN KEY (person)
                          REFERENCES people
                                     (id),
              FOREIGN KEY (role)
                          REFERENCES roles
                                     (id));

Upvotes: 6

Fikret Basic
Fikret Basic

Reputation: 360

I would go with the second approach. It even corresponds to the standard SQL rules.

There are some modifications that you can do in case your types of casts (director, writter and so on...) have also some additional attributes (columns). In that case it is recommended to make them as subtables of the main table.

In your case, the main table would be "cast". It would have ID and other columns. The other tables would be created and would correspond to different cast members, like table: "director", "writter", ... Then do the 1-1 relation with the main "cast" table between every subtable. The relation would be mandatory on the subtable (meaning that e.g. "director" must have the relation to "cast" when it is created). In relational sense, the foreign key for the "director" would be also the primary key for it and it would imply to the primary key of the "cast".

This approach is recommended since not only you can add additional columns to the different cast members, but also other relations in case you want later to expand your database. You can also add additional subtables of the "cast" without changing anything in terms of the structure relative to the "movies" tables.

Upvotes: 4

Rishikesh Dhokare
Rishikesh Dhokare

Reputation: 3589

Second approach seems to be cleaner than the first one. Instead of maintaining 3 different tables you can just get the type in one column.

Further, you can use a bit(0=director, 1=writer, 2=actor) instead of varchar(10) for storing type.

Upvotes: 0

Related Questions