1 2 Previous Next 21 Replies Latest reply on Apr 5, 2016 12:45 PM by BenBW

    Table relationships structure

    BenBW

      Hello

       

      I'm trying to create a database for theatre performances. So I have:
      a table for data about each play itself;

      a table for characters in the play (a many to one relationship to the play);

      a table for each run of the play (e.g. city, theatre, date) (many to one);

      a table for the actors in each run (many to one).

       

      My problem is how do I link the characters in the play to the actors, as the actors are already linked to the performances and the characters are already linked to the play?

      Ideally, I want to query the database for things like: all the performances/roles in an actor's career; all the plays in one city in one year; all the actors who played a particular role.

       

      Filemaker is offering to create another instance of the Actors table. Is that the way forward?

       

      I'm on Filemaker Pro 11, on a Mac (El Cap).

       

      Thanks

        • 1. Re: Table relationships structure
          erolst

          BenBW wrote:

          My problem is how do I link the characters in the play to the actors, as the actors are already linked to the performances and the characters are already linked to the play?

           

          That's not correct; an actor is never linked directly to a play, but only indirectly by his/her playing a character of the play in a given performance.

           

          Character (id_play) >-- Play --< Performance (id_play, id_city, date) --< Appearance (id_performance, id_character, id_actor) >-- Actor

           

          [with another occurrence of Characters connected to Appearance, and another table City (connected to Performance).]

           

          where Appearance is an actor in a role (playing a character); so the idea may be to take the character list as a blueprint that you copy into the Appearance table, then specify the actor for every character.

           

          This may get (a bit) more complicated if you want to also manage theatres/troupes/ensembles.

          BenBW wrote:

          Filemaker is offering to create another instance of the Actors table. Is that the way forward?

          For a complex solution, you will usually need several occurrences of the main entities (tables). But this is not the pertinent point here; that is to get your basic structure correct.

          • 2. Re: Table relationships structure
            BenBW

            Yes, I think I got that actors wouldn't be linked to plays. But I see from what you say that I need more tables. As ever.

            Here's my relationship graph. I've tried to create some forms for entering data, but it won't let me put any values in.

            Screen Shot 2.png

            • 3. Re: Table relationships structure
              erolst

              Looking at your structure, another thing you should have is a Role table, so you can do this:

               

                                      Role

                                          |

                                          ^

              People --< Appearance >-- Play                      

               

              which solves several problems that you'd have with separate Librettists, Composers, Actors tables:

               

              1. You can effortlessly create complete oeuvrographies (for lack of a better word) even for people that are involved in a work in several … well, roles if you manage them all in the Appearances table.

               

              2. You can show a complete list of people involved in a play, or split that list (e.g. using filtered portals), which is much easier than to consolidate several different lists.

               

              3. Composers, Singers, Actors etc. are all people, so all these tables are actually a single one: People.

               

              Let me add that my hobbyhorse is a movie database where all these principles are embodied; roles allow you to add the same person to a movie in different roles (director, screenwriter) or have several people for the same role (e.g. multiple screenwriters).

               

              It is possible that you need some additional things for theatrical plays and and their performances, but the basic principles are the same.

               

              Speaking of which: I recommend you read the section about Database Design in the FileMaker Help; that should give you a good start into that topic.

              1 of 1 people found this helpful
              • 4. Re: Table relationships structure
                BenBW

                Many thanks. I also have a big O'Reilly book on FM11, but sometimes it still doesn't help me get round the conceptual problems.

                 

                Any ideas on why I can't enter data in my forms? I'm using several different portals. Perhaps I haven't ticked the boxes to add data across the relationships.

                 

                Will have another go. Many thanks again.

                • 5. Re: Table relationships structure
                  erolst

                  ideas on why I can't enter data in my forms? I'm using several different portals. Perhaps I haven't ticked the boxes to add data across the relationships.

                   

                  Not without seeing the database.

                   

                  Try the primary sanity check: is there an active record when you try to enter data?

                   

                  If the found set is 0, you cannot enter data because there is no record to enter it into, nor can you add related data because without an active record there aren't any related records.

                   

                  btw, you need to check the option to “Add data … via this relationship” to create related records via a portal – which of course also requires an active record that has valid data in its matchfield(s) as per the relationship definition (that, btw, is why the parent matchfield usually is a primary key with validation to ensure the field is not empty and has a unique value …)

                   

                  One more thing: if you consider the question to be answered, please mark it thus.

                  1 of 1 people found this helpful
                  • 6. Re: Table relationships structure
                    BenBW

                    Hmm. Something's very wrong. Here's the actual db file.

                     

                    I can't figure out what I need to do to make entries link to the right bits.

                    • 7. Re: Table relationships structure
                      BenBW

                      Nearly getting there: but something's wrong with the relationship between Roles and Appearances and People.

                       

                      Thanks

                      • 8. Re: Table relationships structure
                        fitch

                        Interesting problem. Here I used Appearance more or less in the way erolst described. But where he linked from Appearance to the Opera directly, here we have Performance as a join table.

                         

                        I've added color to indicate which table occurrences share the same base table. Roles for example, is linked to Appearance so we can track which performer played that role in a given performance. But it's also linked directly to the Opera as Characters so you can simply see what roles a given opera has. If the graph got much more complex I'd probably use a more consistent naming convention but for simple graphs, using more natural names is OK IMHO.

                        opera.JPG

                         

                        One thing that bothers me a little is that I'm using Appearance (as Composers) to join Opera and (Composers) People using the Opera ID; and joining Performance to Person using the Performance ID. We're still talking about people, but these people don't change from one performance to the next, they belong to the Opera. Is that difference fundamental enough to warrant its own separate join table? Maybe.

                        1 of 1 people found this helpful
                        • 9. Re: Table relationships structure
                          BenBW

                          Many thanks to all. Though I'm not quite sure how Character and Role link up (they are essentially the same thing). Characters are an attribute of the Opera, but obviously for each performance, a singer takes that character/role.

                           

                          I guess this is going to take some time to sort out, before I even start on the data. It's for my PhD, and the clock is ticking!

                           

                          Thanks again.

                          • 10. Re: Table relationships structure
                            fitch

                            I was thinking those names would help you distinguish the functions of the two table occurrences, but maybe I should have named them Opera Role and Appearance Role. They are exactly the same table, same data. They just permit you to see the data from different contexts.

                             

                            E.g., when I'm entering a new Opera, I create the Opera record, then I might create the Opera Role records via a portal. Simple. Then when I'm adding a Performance, again I might use the Opera Role relationship to display the roles for that opera, so that I can create the Appearance records. But then when I'm looking at a Performance, I'm going to want to see who played the roles, so for that I make a portal to Appearance, and display the fields from Appearance::Person and Appearance::Role.

                            1 of 1 people found this helpful
                            • 11. Re: Table relationships structure
                              BenBW

                              OK. I'll have to tinker with this and see if this improves matters. Thanks. The problem I'm having at the moment is that I don't know what table to base the portal in a form on. The form is for entering data about a performance, so the portal needs to list the roles, for me to enter the singers' names for that performance.

                              However, that's probably getting ahead of myself if the data structures aren't right to start with.

                               

                              Thanks again. Will let you know how I get on.

                              • 12. Re: Table relationships structure
                                erolst

                                BenBW wrote:

                                 

                                Many thanks to all. Though I'm not quite sure how Character and Role link up (they are essentially the same thing). Characters are an attribute of the Opera, but obviously for each performance, a singer takes that character/role.

                                Ben  –

                                 

                                sorry for not getting back to you earlier, but with Tom you're in good hands.

                                 

                                I'm afraid you misunderstood my original recommendation.

                                 

                                “Role” is not a character, it's an “activity” – Actor, Director, Composer, Librettist, you name it. Obviously the usual designator “role” is a bit misleading in a theatrical context …

                                 

                                So a play has a list of characters and it won't change because the play has already been written. Use this as a blueprint to create a cast list for a performance where a person in the role of an ACTOR plays a CHARACTER; store this in Appearances, give every actor the role ACTOR, and add DIRECTOR, DESIGNER and other roles as necessary. (Create from a blueprint / copying means: use a script to create one record in Appearance for every record in Character, copying over the respective characterID.)

                                 

                                To reiterate:

                                 

                                Play --< Character

                                 

                                Play --< Performance --< Appearance >-- People

                                 

                                so for every Performance, you copy the Character records of the Play into Appearance and add the respective actor.

                                 

                                An appearance is a join of a performance, a person and a role – and if that role is ACTOR, then it also joins Character.

                                 

                                In English: Suzy Creamcheese (person) plays (role: actress) Ophelia (character) in the April 5th, 2016 performance of Hamlet in The Old Mill, London (performance).

                                1 of 1 people found this helpful
                                • 13. Re: Table relationships structure
                                  BenBW

                                  Ah. Useful terminology clarification!

                                   

                                  Thanks.

                                  • 14. Re: Table relationships structure
                                    BenBW

                                    I 've rebuilt the whole thing from scratch, but I still can't enter any data in my forms' portals. I've ticked the boxes to allow entry creation via all the relationships.

                                    Is there something wrong with this? I'm thinking I shouldn't bother having People altogether, as it seems to cause more problems that its worth.

                                     

                                    Currently, however I try to add a portal containing the Character names for an opera, combined with the Singers' names (so I can add singers to the character for each performance), I just get only the first data entry for People (which isn't a singer) and the first entry for Characters.

                                    Any attempts to add new items in the portal just change the first entry and create duplicates.

                                    1 2 Previous Next