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 ?
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.
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения