The right or effective way to develop multiple many-to-many relationships originating from the same table

Question

I am developing a movie cataloger that will rely on an SQL database to store data. I'm not a database developer, and frankly, I'm not so seasoned with anything other than basic database queries. My question is the most efficient way to do the following.

Currently I have created 5 tables

Movies ------ [id] integer [title] nvarchar(100) [duration] integer [year] datetime People ------ [people_id] integer [person] nvarchar(100) Writers ------- [id] integer [people_id] integer Directors --------- [id] integer [people_id] integer Actors ------ [id] integer [people_id] integer 

Basically, many, many relationships using the conversion table, films-> writers-people, films-> directors & People, and finally films-> actors and people. The People table is a pool from which you can extract the data needed for each role. Since a person can be a director and a movie star, writer and director, or even all three roles, I figured these three transition tables would solve this. Naturally, a person can be in many films. So I decided that this is the way to do it.

I read about how to set up many relationships in many web articles, and the more I read, the more I get confused about how to set up this situation. Most of them simply do one field or use an analogue of the author / book, which does not help me understand how to implement my situation.

As said earlier, my question is, is this an effective way to do this? Or even right?

I want to be able to easily request a movie and get all the information related to it in a form.

Thanks -Res

Answer 1

Do not create separate tables, create a new table, such as PeopleRoles , which contains 'Actor' , 'Director' , etc., then a relationship table, to immediately join movies, people and roles:

 Movies ------ [id] integer [title] nvarchar(100) [duration] integer [year] datetime People ------ [people_id] integer [person] nvarchar(100) PeopleRoles ----------- [role_id] integer [name] nvarchar(100) MovieCollaborators ------------------ [id] integer [people_id] integer [movie_id] integer [role_id] integer 

You will get the flexibility and the ability to easily get all the co-authors from the movie with one JOIN .

Answer 2

You mimic inheritance, but your derived types do not have any different fields, so you could also put them all in one table and have a discriminator column to indicate their types.

Another difficult part of this modeling problem is that you can force the same person to perform many roles, for example. Write, directly, a star. I think you need to introduce the concept of role. Then it is connected with the Roles not with the person. Role classes are a common feature in many models.

Answer 3

Instead of the three many many tables that you proposed, it is better to have one movie table that will have three columns: MovieId, peopleId, roleType

You will recognize the role of man by the role of Type (actor, director, writer)

Answer 4

I would recommend combining authors, directors and actors in one table.

Answer 5

If things remain as simple as they are, you should use the model proposed in the other answers of the same join table that contains the Role column.

However, you can start adding things to your model that apply only to specific roles. For example, is an Actor a leader, support, or a cameo? In this case, the model that you described in your question is better. The disadvantage of your model is that if you want to consider participating in the film in a general way, you need UNION combine many tables.

This is a compromise. A single connection table with a role column simplifies the handling of the role as a whole. Using a separate join table for each role facilitates their handling in different ways.