8 Replies Latest reply on Jan 5, 2009 12:42 PM by TSGal

    Help needed with movie database

    pod2189

      Title

      Help needed with movie database

      Post

      Hi all

       

      I'm newbie to creating databases but i have created a movie database i have 4 tables film, actors, producers & jointable.

      My problem is that i get no serials in to jointable and i don't know why the relation are as follow in film i have film_id related with film_id in jointable, in actors i have actors_id related to actors_id in jointable and in producers i hav prod_id with prod_id in jointable and i want the serials from actors, film and producers inserted automatic to jointable. could any one help with this please.

       

      Thanks in advantage and excuse me if my english is a little bad 

        • 1. Re: Help needed with movie database
          pod2189
            

          Hi guys

           

          No one to help me with my problem with the jointable i can't get any data into it if i don't do it manually and that will be a huge work with over 600 DVD films in the database, Please please please help me anyone.

           

          Thanks in advantage 

          • 2. Re: Help needed with movie database
            jeanwm
              

            Your setup seems a little strange.

             

            Your single join table is related to the 3 other tables ?

             

            How do you enter the data in the different tables film, actors and producers ?

            • 3. Re: Help needed with movie database
              pod2189
                

              Hi Jean WM

               

              My setup inthe different tables are as follow.

               

              In film table i have a field called PK_film_ID wich are linked to FK_filmID in jointable.

              In actors table i have a field called PK_actors_ID linked to FK_film_ID in jointable.

              In producers table i have a field called PK_producer_ID linked to FK_producers_ID in jointable.

               

              There are a many to many relation between films and actors.

              There are a many to many relation between actors and producer.

              There are a many to many relation between films and producers.

               

              so when i create a new post in films there will be a pk_film_id automatic and then i want that one go into fk_film_id in jointable and the same procedure for actors and producers.

               

              Filmtablesetup

               

              pk_film_id

              Title

              released

              Category

              and so on

               

              Actorstablesetup

               

              pk_actor_id

              Name

              bio

              and so on

               

              Producertablesetup

               

              pk_producer_id

              name

              bio

               

              Jointablesetup

               

              join_id

              fk_film_id

              fk_actor_id

              fk_producer_id

               

              Thanks Jean for trying to help me 

              • 4. Re: Help needed with movie database
                jeanwm
                  

                 

                I'm afraid you will have to re-think your relationships.

                 

                >>There are a many to many relation between films and actors.

                There are a many to many relation between actors and producer.

                There are a many to many relation between films and producers.<<

                 

                You have to resolve the many to many relations into two one-to-many relationships.

                 

                You have 3 base tables: film, actor and producer.

                 

                If you want to link the film table to the actor table, you need a 'film-actor' jointable.

                If you want to link the actor table to the producer table, you need an 'actor-producer' jointable.

                etc....

                 

                Make sure all your many to many relationships are resolved in 2 (or more) one to many relations.

                 

                You need a relationship from the film base table to the film-actor jointable and from the film-actor jointable to the actor basetable (or a TO of that base table, depends which system you follow).

                 

                The jointable will hold records with the ID of the film and the ID of the actor, bringing film and actor together in a record.

                There you will have several records with the same filmID but different actorID.

                From the actor ID in the join table, you need a link to the actorID in the actor table.

                 

                Your jointable is so to speak 'between' the 2 base tables and resolve the many-many relation.

                 

                You can specify in the relationship pane whether you want recordcreation through the relationship. That wil in part resolve your first problem.

                 

                HTH

                 

                 

                • 5. Re: Help needed with movie database
                  pod2189
                    

                  Hi Jean

                   

                  I have done what you tell me so i have 2 join tables filmactor & filmproducer but i still can't get it right with the serial numbers

                   

                  in relation pane i have as follow

                   

                  pk_film_id = fk_film_id

                   

                   

                  Film

                  delete posts in this table when posts are deleted in the other table

                   

                  Filmactor 

                  allow posts to be created in this table when 

                  posts are created in the other table

                  delete posts in this table when posts are deleted in the other table

                   

                  Thanks again for trying to help me 

                  • 6. Re: Help needed with movie database
                    jeanwm
                       Caution:

                    >>delete posts in this table when posts are deleted in the other table<<

                     

                    Do not use that as long as you do not have a full understanding of the relationship mechanism.

                    I would suggest that you uncheck that for now.

                     

                    Lets concentrate on the part actor-film.

                     

                    You have the actor table and the film table, with the actor-film table in between the two as jointable.

                     

                    In the actor table you have pk_actorID.

                    In the film table you have pk_filmID.

                     

                    In the jointable you have fk_actorID and fk_filmID.

                     

                    In the relationship graph, link the pk_actorID with the fk_actorID in the jointable and the pk_filmID with the fk_filmID in the jointable.

                     

                    Start to play around with the 'create related records' feature to see what happen in all trhe cases.

                    Make sure you are familiar with it.

                     

                    Now you have the basis to do the other relationships.

                    • 7. Re: Help needed with movie database
                      pod2189
                        

                      Hi Jean

                       

                      I have done all that you told me to do and i have playing around with

                      allow creation of records in this table via this relatioship

                      but i can' get anything to shown up in film-actor table.

                       

                      pk_filmid  1 in film table

                      title         Cassablanca

                       

                      pk_actorid 1 in actor table

                      name Harpo

                       

                      fk_filmid this is empty in film-actor table

                      fk_actorid this is emty in film-actor table

                       

                      fk_filmid 1 i want it like that

                      fk_filmid 1 i want it like that

                       

                      but i can't get it right

                       

                      Thanks again Jean 

                       

                      • 8. Re: Help needed with movie database
                        TSGal

                        pod2189:

                         

                        Thank you for your posts.

                         

                        Let's go to the Join table.  Create a new record and enter into fk_actorid the value 1.  See if "Harpo" now appears in the portal.  If not, then you don't have the Relationship set up properly.  Go back into Manage Database, click on the Relationship tab and make sure that fk_actorid from the Join Table is connected to pk_actorid in the Actor Table.  Once established and verified, then return to Browse mode and go to the layout that has data for the Actor table.  Find pk_actorid = 1, and make sure it exists.  If it does, then return to the Join table layout and see if there is now a value in the Actor portal.

                         

                        Do the same for the Film table.

                         

                        Let me know if you are still running into difficulty.

                         

                        TSGal

                        FileMaker, Inc.