Straight from the FileMaker Training Series book is a few pages about data modeling, specifically your situation (3-26 to 3-27 if you're following along in your text book). It goes into pretty nice detail explaining actors, roles and movies.
Basically shows 3 tables:
Actor Role (join table) Movie
Actor ID pk Role ID pk Movie ID pk
First Name Actor ID fk Name
Last Name Movie ID fk Date
Year of Birth Character Name
I don't have that book, but maybe it's something I should get, especially if it discusses the exact project I'm interested in.
Right now, I would like to know why I need three tables and not two.
You have a many to many relationship. An actor can appear in many different movies. (And can even have more than one role in the same movie such as you see with Dick Van Dyke in Mary Poppins). But a Movie can have many different actors in it.
Thus, you need a Roles table serve as a join table linking actors to movies. On your Actors layout, a portal to roles lists the roles and movies in which that actor has appeared. On your movies layout, a portal to roles lists all the roles that exist in the script of that movie.
You are the first person who explained it to me in a way I am able to understand. Thank you!
I honestly had no idea my database was many-to-many.
But...not 100%. I'm still fuzzy on why a third table is needed. For my purposes, the roles an actor portrays--even if he has more than one in the same movie--is not important. What's important to me is who is in the movie.
I'll use Dr. Strangelove as an example. The cast includes George C. Scott, Sterling Hayden, Slim Pickens, James Earl Jones, and Peter Sellers.
Peter Sellers has a few roles in the movie, but that's not important to me. What's important is only that he's in it. I am not going to design a database with a field for actors' roles. Just their presence in the film is what's important. I can use IMDB.com to see the roles.
My apologies if I didn't make this clear to begin with.
Does this mean I still have to have a third table? And does this mean I need a separate layout for actors? I am trying to avoid designing a database that's going to be over my own head. A third table might do that. If it's necessary, then so be it, but is it?
Yes, but Peter Sellers didn't appear in just this one movie. Think of all those Pink Panther movies!!
So even if you are not interested in documenting the roles played by each actor, you still need the join table to link one actor to many movies and one movie to many actors.
1 Dr. StrangeLove
2 Pink Panther Returns
1 Peter Sellers
2 Sterling Hayden
That data shows that Peter Sellers appeared in both Dr. Strangelove and Pink Panther Returns but that Sterling Hayden only appears in Dr. Strangelove. (I'm not a movie buff so I really don't know what movies Sterling Hayden appeared in, this is just an example of how the join table works.)
To list the actors that appeared in a movie, you'd add a portal to the Join table on your Movie layout and include the name field from the Actors table in the portal row. In similar manner, you can put a portal to the join table on the Actors layout and include the movie title field from the movies table to list all the movies in which that actor appeared.
Here's how I created the tables:
(There will be other fields in this table, but only the ones above are essential for right now.)
Did I leave anything out? Does anything have to be added to the Join table?
I now have to figure out what fields to join. Also, I have to figure out how to design the layout so I can add multiple actors. How do I do that?
And I need to understand the following statement: "To list the actors that appeared in a movie, you'd add a portal to the Join table on your Movie layout and include the name field from the Actors table in the portal row."
This statement is a bit beyond me, but I'll worry about portals after I get the tables, relationships, and layout designed properly.
First, make sure that Movie::Movie ID and Actor::Actor ID are defined as auto-entered serial number fields. You can double click these fields in Manage | Database | Fields to open a dialog where you can select this auto-enter option for them.
Then, make sure that Join::Movie ID and Join::Actor ID are number fields that DO NOT auto-enter a serial number.
Then go to Manage | Database | Relationships and drag from
Movie::Movie ID to Join::Movie ID
Then drag from Actor::Actor ID to Join::Actor ID.
You will probably then want to double click the relationship lines thus created so that you can select "allow creation of records via this relationship" for the join table in both of these relationships. This will make it easier to add new record to the Join table when creating records in it to link an actor to a movie.
Okay, I did all that. Now, I need to understand how to ENTER the data. Will I be able to do that automatically once the tables are designed, and the relationships are created? In other words, once all of the above is done, how do I enter more than one actor into a record?
You don't enter more than one record in to an actor. Each actor is entered exactly once in the Actors table.
But you use a portal to Join to create a record that then links a given movie to a selected actor and since a portal can show multiple records, by adding multiple join table records in it, you link th ecurrent movie to multiple actor records.
First use the portal tool to add a portal to Join on your Movie layout. add the following two fields to the portal:
Join::Actor ID and Actor::Actor
Format Join::Actor ID as a drop down list. Give it a value list that uses the specify values from a field option to list ID numbers from Actor::Actor ID. Click the "also display values from second field" option and select the Actor::Actor field for the second field. With this value list, you can see the actor's name in the value list so that you can select the actor by name, but the value list enters the Actor::Actor ID value to link that record to an existing Actor record.
Now, assuming that you enabled "allow creation..." in the relationship correctly, you can add actors to the list for given movie by selecting them from this drop down--each time making this selection in the blank "add" row that the "allow creation..." option causes to appear below the last actual record in the portal.
Please note that these are still "baby steps" techniques. More sophisticated and more user friendly options are also possible.
I understand some of what you just wrote, but not all of it.
I do understand there will be only one listing per actor in the Actor table.
I didn't understand the portal information, because I've never made one. I'll need more help with that.
I do understand what a drop-down menu is. However, I do not believe the use of a drop-down field will be practical for me, because there will be hundreds of actors in the list. Possibly thousands! My movie collection is HUGE, and it contains silent films, foreign films, documentaries, animation, and they'll all have actors. I really would prefer to type in the names of actors manually, if that can be possible.
One other thing, which may change the way this database is designed. At first, I was going to have one field for the actors' names. I was going to enter the data like this: Sellers, Peter.
I've changed my mind. I want two fields for an actor's name: Last Name and First Name. I think it will ultimately be easier to perform searches this way. I'm assuming this will complicate the creation of this database.
Right now, I'm still confused about how to enter actors' names into a record. Can we focus on that a bit more, please?
I do understand what a drop-down menu is. However, I do not believe the use of a drop-down field will be practical for me, because there will be hundreds of actors in the list.
Please note that these are still "baby steps" techniques. More sophisticated and more user friendly options are also possible. This includes a number of options for working with large lists of values. But they will still build from this initial set up so set it up this way to start as a way to confirm that you have your basic layout and relationship designs working for you.
To enter an actor's name into a record, go to your actor layout, select new record and enter the actor's first and last names into the first and last name fields.
This change will require an additional field in your Actor table. Define a text field, FullName and define it to auto-enter this calculation: Last Name & ", " & First Name. Click the Validation tab and specify "Unique values" on this field as a way to guard against entering the same actor name twice. Then update your value list to use FullName as the second field in the value list.
Thank you for your help today. I'm done for the day, so I will read what you've taught me, and let's see how things go tomorrow. Thanks again! Bye for now.
Okay....another day, another challenge. One thing you said is unclear to me, so I'd like to ask you to clarify something, please.
"To enter an actor's name into a record, go to your actor layout, select new record and enter the actor's first and last names into the first and last name fields."
I only have one layout as of now. Are you saying I need more than one layout? Is it possible to do this without additional layouts, and just have one?
When you say to enter an actor's name into a record, I'm a bit confused by this. Are you saying I have to enter the actor's names into the Actor's table first, and separately, and then enter the movie information? I'm unclear about this.
Or, are you saying I need two layouts...one with the movie information (i.e. title, director, etc.), then to enter the names of multiple actors, go into the Actors layout and do that? If this is correct, then do I do this before or after a portal is created?
Sorry for inundating you with questions, but with each one, I learn more. Thank you! PT
I only have one layout as of now.
Better pull down the layouts drop down in the status tool bar and check again. This is not true unless you specifically deleted some layouts. FileMaker automatically creates a corresponding layout each time you define a new table in your file. It will have exactly the same name as the table you created. You can also create as many additional layouts as you need by selecting "New Layout" from the layouts menu while in Layout Mode.
Is it possible to do this without additional layouts, and just have one?
It is possible, but may not be practical. Much depends on how much data you want to record in each table and how you want to control the data entry/review/editing process.
FileMaker solutions usually have much more than one layout--hundreds of different layouts are not unheard of. And even if you produce a final design that does everything on one layout, it's a very good idea to keep at least one "utility layout" for each of your tables to use for both "behind the scenes" manipulation of your records via scripts and as a way to get a quick view of the data in that table when you are either modifiying the design or checking to see if all is working correctly.
Keep in mind that what I am describing is still a "beginner level" way to add a new actor to your list so that you can then link that actor to various movies via entries in the join table.
Are you saying I have to enter the actor's names into the Actor's table first, and separately, and then enter the movie information?
With the current "beginner level" state of your design, yes, you enter data for new actors on the actors layout and data for new movies on the movies layout. The portal that you can place on either layout then serves to create records in the join table in order to link a movie to an actor.
Once you have this basic level design working so that you can see the principles involved in action, we can look at multiple options that gradually enhance this basic design. I even have a demo file that you can download that illustrates some basic design options for a many to many relationship, but don't want to spring that one on you until you have the basic level design in place and working for you.