TaiChi56 0 Posted February 14 Share Posted February 14 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. Link to post Share on other sites
comment 1,799 Posted February 14 Share Posted February 14 Do you need to import the movies too? Or do you already have all of them in your Movies table? Link to post Share on other sites
TaiChi56 0 Posted February 14 Author Share Posted February 14 Yes, I am importing all the movies also. They are listed under title. Link to post Share on other sites
comment 1,799 Posted February 14 Share Posted February 14 (edited) 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 by comment Link to post Share on other sites
TaiChi56 0 Posted February 14 Author Share Posted February 14 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. Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now