8 Replies Latest reply on Aug 7, 2017 5:18 AM by beverly

    Multi-relational DB question(s)


      I am trying to create a database to keep track of my theatre-goings, as well as retain other useful information


      In a perfect world, each show would have only been produced once, each production would only have been seen once, each theatre would only have had one name, and each theatre would only have one stage (space). As I was working on this, pretty much ALL of the above turned out not to be true.


      What I started working on was a flat database, just keeping track of ticket stubs. Each stub, and therefore, each viewing, even if it were for a different performance of the same show, would be a new record. I used self-relationships and portals, to create lists of which theatre and date I've seen that show, and which shows were performed at that theatre.  Downsides it doesn't show me any shows that might have been in that same space under a different name, And shows in that same space show multiple times, depending on how many times I saw it!


      As the project grew, I thought I could keep a separate table for the show, perhaps where I can add other relevant information to the show, such as opening/closing dates, and other things I may care to keep track of. Perhaps even the show poster.  And a separate table of theatres. And THERE'S where it gets complicated. Although most theatres only have one performance space, some have multiple, such as New World Stages, and Theatre Row. I'd like to treat these as one theatre, rather than separate theatres, even though they have different stages within.


      So I created another table of theatres, which would retain the theatre address, alternate names for the theatre (did that as a repeating field), spaces within the theatre, although in most cases this isn't necessary (a repeating field of 5---so far the largest number), and a matching repeating container field to house the seating chart.


      Hopefully joining all this, would be a ticket sale table, joining Theatre, Show, and the date I saw it, seat, etc.


      Where the theatre stuff gets confusing is the spaces. Though i want each theatre, even if it has only one space, to have a seating chart, in those cases I'm NOT using the "Spaces" repeating field, but AM using the first repetition of the Container that I intended to be matched to it., so the theatre WOULD be the space, so to speak. I was thinking of making those a separate table, and have a related portal showing space names and chart(s), and only those needed, not five if I only have one or two.


      And NOW, I'm getting really confused. I am a long time user of Filemaker, but the multi-relational stuff fairly new to me and driving me nuts to wrap my head around it.


      HELP! Can someone point me in the right direction here?



        • 1. Re: Multi-relational DB question(s)

          First rule of linking tables in a relational database: don't use names. Use proper primary keys that:


          a) are unique and devoid of any added "meaning".

          b) are assigned when the record is created and never, ever changed.


          In FileMaker, that is usually accomplished with either a UUID or an auto-entered serial number. If a theatre changes its name, you just edit the name field to record the name change and the primary key or ID field is left unchanged and any records linked to that table remain linked.


          I'd start with this basic set of relationships:


          Theatres-----< Spaces ----< performances ----< Tickets (maybe seats)


          If I wanted to see how many times I've seen "hamlet" even if they were different productions, I'd add a table of Plays to link to productions by a Play ID.


          Each of the above link like this from left to right:

          Theatres::__pkTheatreID = Spaces::_fkTheareID


          __pk is my naming convention for a primary key.

          • 2. Re: Multi-relational DB question(s)

            Hi Joseph,


            I know this is not a direct answer to your situation but I think you need to review the Filemaker Training Series. The link I am providing is for FTS 15 Advanced:




            There are a lot of examples and explanation that will help you in relational design. The amount of time you save will be worth the effort to review the FTS in the long run.  Even though it is for v 15 the principles still apply.


            I used FMP for years before I read the FTS. I just wish I had studied it a lot sooner…




            2 of 2 people found this helpful
            • 3. Re: Multi-relational DB question(s)
              Jens Teich

              This is called data modeling and its rules are kept secret normally.  Database masters pass their knowledge in a full moon night to their students ... If you want to strengthen your skills have a look at advanced version of (FileMaker Training Series | FileMaker ), chapter Data Modelling

              • 4. Re: Multi-relational DB question(s)

                LOL Jens is joking about being secret, of course. Since that each database may be slightly different from the next one, it is rare to find a one-data-model-fits-all.

                I tend to approach the problem from the "what do I need for a report/printed list or form?"  This gives me clues on what is a record and what might be repeated , thus needing related data.   I can make every guess in the world creating the model (what data goes where, what tables & what fields). But if I don't have final outcomes, I can guess very incorrectly.

                Next I assess how I'm going to get the data in for that final reporting. Adjustments may need to be done to the model that relate directly to data entry.

                Along with the FTS to help you create the tables, fields and relationships, you are very well ahead on the design.

                Will it be perfect this way? Likely not and it will still be a learning process. But a better base and end goal can only help!


                Sent from miPhone

                • 5. Re: Multi-relational DB question(s)

                  There is also a specific database design course (not the regular FIemaker course) on Lynda.com for FILEMAKER.

                  1 of 1 people found this helpful
                  • 6. Re: Multi-relational DB question(s)

                    Full moon tonight! Where does this passing if knowledge occur?   Do I need the official FileMaker robes or is casual attire ok?

                    • 7. Re: Multi-relational DB question(s)

                      As pointed out by others already, get your data model right and the rest will flow more smoothly. By way of variation on Beverly's approach, I think of each table as an entity and fields as characteristics of that entity. What entities are you dealing with here? Theatres. But that is insufficient to adequately describe what it contains. Some theatres may contain just one stage, but others may have multiple stages, so another entity is Stages (or performance spaces). We regularly attend a small theatre that has two performance spaces (stages). We also regularly attend a cinema which has four performance spaces (screens). To my mind you should forget about repeating fields and have one table for Theatre, another for Performance Space (and perhaps even another for Theatre Name). In the end this will be much easier to manage and develop.

                      2 of 2 people found this helpful
                      • 8. Re: Multi-relational DB question(s)

                        "Database Design for Mere Mortals" - a very good book. There may be an electronc version.