AnsweredAssumed Answered

Multi-relational DB question(s)

Question asked by JosephD.Marchione on Aug 5, 2017
Latest reply on Aug 7, 2017 by beverly

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?