Menu
I'm designing a movie cataloger which will rely on a SQL database to store the data. I'm not a database design pro, and honestly I'm not so seasoned in anything but basic database queries. My question is what is the most efficient way to go about the following.
I currently have 5 tables created
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 a many to many relationship using a junction table, Movies->Writers<-People, Movies->Directors<-People, and finally Movies->Actors<-People. The People table being the pool from which to draw the data needed for each role. Since a person could be both the director and star in a movie, a writer and director, or even all three roles, I believed that the 3 junction tables would solve that. Naturally a person can be in many movies as well. So I figured this was the way to do it.
I read up on setting up many to many relationships via several web articles, and the more I read, the more I get confused on exactly how to setup this situation. Most just tackle a single field or use an author/book analogy, which doesn't help me understand how to implement my situation.
As stated earlier, my question being, is this an efficient way to do this? Or even proper?
I want to be able to easily query a movie, and get all information related to it out to a form.
Thanks -Res
407
When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.
When you have a many-to-many relationship between dimension-type tables, we provide the following guidance: Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column. Add a bridging table to store associated entities. Create one-to-many relationships between the three tables.
How to implement one-to-many relationships when designing a database: Create two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.
To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. We have developed a relationship between the Teachers and the Courses table using a foreign key.
Don't make separate tables, create a new table like PeopleRoles that contains 'Actor', 'Director' etc., then a relation table to join movies, people and roles at once:
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 gain flexibility and the ability to easily get all collaborators from a movie with a single JOIN.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
