Movie Database Multiple Actors, Directors Joining table

[TaiChi56]

I have been working on a movie database and found a schema that makes sense to me as far as the relationship. But one thing I am having problems with is I am importing an Excel Spreadsheet with 715 rows of data. My problem is that each Movie has four actors, two main ones and the other two character actors. I have separated them into for different columns in EXCEL. Actor_1F is the Actors first name and Actor_1L Last name, I did this for each of the four actors. I really do not think this is correct. Because when I look at the Schema I cannot figure out how to relate the four actors to a movie.

What I am doing is showing the picture of each actor and giving their Bio. The way I import the 715 rows is by using their name attached to an image field. I hope this is making sense. So long story short, How would I relate the four actors to a movie when importing in bulk, when they are separated like this. Here is an example of the EXCEL Spreadsheet? I have not finished separating the Actors first and Last names yet. Thank you. 

 

[comment]

Do you need to import the movies too? Or do you already have all of them in your Movies table?

 

[TaiChi56]

Yes, I am importing all the movies also. They are listed under title. 

[comment]

So, if you are just starting with your database, I would suggest you do the following:

First, do NOT separate the actors' full names in Excel. Leave that for later. Now:

1. Define a table of Cast with the following fields:

• CastID - Number, Auto-enter serial number
• MovieID - Number
• ActorID - Number
• MovieTitle - Text
• ActorFullName - Text

2. Import the Excel spreadsheet into the Cast table 4 times: the 1st time, import the Title column into the MovieTitle field and the Actor_1 column into the ActorFullName field. The 2nd time, import the Title column and the Actor_2 column. The 3nd time, import the Title column and the Actor_3 column and finally import the Title column and the Actor_4 column. 

3. Define a Movies table with the following fields:

• MovieID - Number, Auto-enter serial number
• Title - Text

4.  Import the Excel spreadsheet into the Movies table, mapping the Title column into the Title field.

5. Define an Actors table with the following fields:

• ActorID - Number, Auto-enter serial number
• FullName - Text, Validate as Unique, Validate always
• FirstName - Text
• LastName - Text

6. In the Cast table, make sure all records are found. Import the Cast table into the Actors table, mapping the ActorFullName field into the FullName field.

7. Define the following relationships:

• Cast::MovieTitle = Movies::Title
• Cast::ActorFullName = Actors::FullName

At this point, you will already have a working relational database, with all the data imported from Excel. Now you can change the relationships to use IDs instead of names:

8. In the Cast table, make sure all records are found. Click into the MovieID field, and select Replace Field Contents… from the Records menu. Specify a calculated result = 

Movies::MovieID

and click Replace. Next, click into the ActorID field, and replace its contents with = 

Actors::ActorID

9. Change the relationships to:

• Cast::MovieID = Movies::MovieID
• Cast::ActorID = Actors::ActorID

Now you can split the actors' names and remove the validation from the FullName field (or delete the FullName field altogether).

Note that this assumes that no two movies have the same name and likewise for actors.

 

 

Edited February 14, 2021 by comment

[TaiChi56]

Wow, thank you for taking the time to write this. I will do what you suggested and go from there. Again, thank you very much. 

[Richard James]

On 2/14/2021 at 7:43 AM, TaiChi56 said:

I have been working on a movie database at here and found a schema that makes sense to me as far as the relationship. But one thing I am having problems with is I am importing an Excel Spreadsheet with 715 rows of data. My problem is that each Movie has four actors, two main ones and the other two character actors. I have separated them into for different columns in EXCEL. Actor_1F is the Actors first name and Actor_1L Last name, I did this for each of the four actors. I really do not think this is correct. Because when I look at the Schema I cannot figure out how to relate the four actors to a movie.

What I am doing is showing the picture of each actor and giving their Bio. The way I import the 715 rows is by using their name attached to an image field. I hope this is making sense. So long story short, How would I relate the four actors to a movie when importing in bulk, when they are separated like this. Here is an example of the EXCEL Spreadsheet? I have not finished separating the Actors first and Last names yet. Thank you. 

 

The best way is to normalize your data. Instead of keeping four actor columns in the same table, create a separate 'Actors' table and a 'MovieActors' junction table that links movies to actors. That way you can easily handle multiple actors per movie, even if later you want to add more than four

[comment]

6 hours ago, Richard James said:

normalize your data

Gee, I wonder why no one has thought of that in 4 years. Let alone explain exactly how to migrate the data to a normalized structure...