1 2 3 Previous Next 30 Replies Latest reply on Oct 17, 2013 3:51 PM by philmodjunk

    First Relational DB:  Adding Several Actors to One-Layout Movie Database

    PeterTeneldas

      Title

      First Relational DB:  Adding Several Actors to One-Layout Movie Database

      Post

           Hello to you. 

           I have never designed a relational database before, and would like to create a simple one for my movie collection.  I’ve created several flat file db’s, and have read a lot of documentation about one-to-many, but sitting down and planning one out myself is not as easy as I thought.  So, I am hoping I can get some assistance here, please.

           My movie collection database needs to be able to enter and display more than one actor/actress.  Right now, my flat file movie collection database lets me enter only one, unless I add extra fields for additional performers.  I’m assuming I can have numerous actors in a record if I use a relational database.

           I’ll give you an example:  The record for The Godfather should be able to display Marlon Brando, Al Pacino, James Caan, Diane Keaton, and a few others.  The record for Jaws should be able to display Richard Dreyfus, Robert Shaw, and Roy Scheider.

           I’d prefer to have only one form/layout, instead of having more than one.  I’d prefer not to have a separate form/layout just for performers.  I do not know if this is possible.

           I also have not figured out how many tables I will need.  I am assuming I need only two, both with ACTORS as a field.  But I’m not sure about that, either.

           So, may I please request some advice and feedback about this?  How many tables do you think would be required for this?  Can I design the layout to have just one form, or will I absolutely need a separate page just for actors? 

           Can I design the layout so all of the performers’ names are displayed at once, or will I have to scroll through a field to see them all? 

           Is what I want to do easy, as far as a relational database is concerned, or complicated?

           Thank you for any help you’d be willing to provide.  Peter Teneldas

            

           PS:  Filemaker Pro Adv 12.0

        • 1. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
          SteveMartino

               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

                                             Salary

          • 2. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
            PeterTeneldas

                 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.  

                  

                  

            • 3. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
              philmodjunk

                   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.

              • 4. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                PeterTeneldas

                     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?

                      

                     PT

                • 5. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                  philmodjunk

                       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.

                       Example:

                       Movie Table:

                       ID     Title
                       1       Dr. StrangeLove
                       2       Pink Panther Returns

                       Actor Table:

                       ID       Name
                       1        Peter Sellers
                       2        Sterling Hayden

                       Join Table:

                       MovieID      ActorID
                       1                 1
                       1                 2
                       2                 1

                       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.

                  • 6. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                    PeterTeneldas

                         Here's how I created the tables:

                          

                    Movie Table:
                         Title
                         Director
                         Actor
                         Movie ID

                    (There will be other fields in this table, but only the ones above are essential for right now.)

                          

                    Actor Table:
                         Actor
                         Actor ID

                          

                    Join Table:
                         Movie ID
                         Actor ID

                          

                         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.

                          

                         Peter Teneldas

                    • 7. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                      philmodjunk

                           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.

                      • 8. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                        PeterTeneldas

                             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?

                        • 9. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                          philmodjunk

                               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.

                          • 10. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                            PeterTeneldas

                                 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?  

                            • 11. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                              philmodjunk
                                   

                                        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.

                              • 12. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                                PeterTeneldas

                                     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.   

                                • 13. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                                  PeterTeneldas

                                       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

                                  • 14. Re: First Relational DB:  Adding Several Actors to One-Layout Movie Database
                                    philmodjunk
                                         

                                    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.

                                    1 2 3 Previous Next