1 2 3 Previous Next 32 Replies Latest reply on Oct 21, 2015 2:10 PM by vvkozmenko

    Event management solution

    vvkozmenko

      Hi, I am relatively new to FileMaker. For whatever reason, I am having troubles with many-to many relationships and linking table.

       

      I need to create a solution to track social events. At each event there will be multiple participants performing multiple songs. So, during each event the same song will be performed by multiple participants and same participant will perform several songs.

       

      I am totally lost about how to handle it.

       

      Thank you,

       

      Val

        • 1. Re: Event management solution
          erolst

          Maybe see here:

           

          Help With Songwriter Database

           

          and here the official documentation & introduction:

           

          Working with related tables and files

          • 2. Re: Event management solution

            This sounds like a database design understanding issue, not a FileMaker issue per se.

             

            I would check out a Lynda.com course or similar on relational database design before diving in to creating a design which may not work or end up just causing you a lot of problems.

             

            FileMaker also chooses what it "thinks" is the best relationship type between two tables and you can't change that.

             

            HTH

            • 3. Re: Event management solution
              erolst

              morkus wrote:

              This sounds like a database design understanding issue, not a FileMaker issue per se.

               

              Really? What makes you think that?

              • 4. Re: Event management solution

                What made me think that was the wording of the initial posting.

                 

                To me, and I could of course be mistaken, it sounded like the confusion was terminology like "multiple of this which could have multiple of that", etc. (1:M, M:M, or whatever).  Moreover, since I always assume that the person posting has the business knowledge to do what they need to do (that is understand the music business or whatever), I tend to think that some new to FMP may be new to databases too.

                 

                Of course, the poster didn't include their complete technical background...

                 

                You also included a similar "working with related tables and files" link, right?

                 

                I always recommend to people I know to understand SQL and be able to do queries and to be able to design an ERD (not in FMP's simplistic environment) before "jumping in". (Not that you can't eventually learn it by jumping in, of course, it'll, IMHO, just be more frustrating that way.

                 

                Thanks for your reply.

                 

                - m

                • 5. Re: Event management solution
                  erolst

                  Thanks for your thoughtful reply; but, truth be told, I was just being ironic.

                   

                  The OP already mentioned being “relatively new” (whatever that may mean in practice) and “totally lost”, so I thought that the “database understanding issue“ bit was somewhat obvious …

                   

                  btw, the FM Relationship Graph is not really simplistic (nor is it an ERD); it manages to pack a lot of advanced concepts into an easy-to-understand interface (not that that couldn't be improved) which one could call simple – by way of a compliment.

                  • 6. Re: Event management solution

                    Sorry I missed your humor...

                     

                    The RG is OK, but (again, IMHO) a bit too simplistic unless you already understand databases -- at least for anything approaching production complexity.


                    My biggest gripe is that FMP is not OO. Thus, I can't send messages to other objects to change their color, call code, etc. as with modern programming environments. In FM, it's all done in the 1980s way. Sigh. Well, FMP does a lot right and I use it for a lot, and happy to have it; just nothing approaching production work.

                     

                    Thanks,

                    • 7. Re: Event management solution
                      siplus

                      morkus wrote:

                       

                      FileMaker also chooses what it "thinks" is the best relationship type between two tables and you can't change that.

                       

                      ???

                      • 8. Re: Event management solution
                        siplus

                        You already know that many-many rels need a linking table, which is a great starter.

                         

                        Now think about your problem: you have a social event. During that event things will happen, in a sequence, so you will have subEvent1 to subEventN that will happen.

                         

                        what is a subEvent made of ?

                         

                        PositionInEvent (1...n)

                        PerformerID

                        SongID

                         

                        and that is your "linking table". You can add other fields to it, like ApplauseSecondsGot etc)

                         

                        you have another Performers table, with PerformerID and PerformerName (and maybe address, Hourly rate etc)

                        and another Songs table, with SongID, song Name (and maybe composer, songText, etc)

                        • 9. Re: Event management solution
                          vvkozmenko

                          Hi, siplus,

                           

                          I've been thinking the following way.

                           

                          1. Since there are many songs, there must be a "Song" table.

                           

                          SongID

                          SongAuthor

                          SongVersion etc

                           

                          2. Since there are many singers, there must be a "Singer" table

                          SingerID

                          SingerName

                          SingerLastName etc

                           

                          The table that you have suggested, would be a linking table

                           

                          PositionInEvent (1...n)

                          PerformerID

                          SongID

                           

                          Please correct me if I am wrong, PositionInEvent (1...n) is an equivalent of LineItem that is used in the Customer/Invoice example in all FileMaker tutorials, right?

                           

                          I am confused where in this database structure the table "Events" belongs. I am envision the table "Event" with the following fields

                           

                          EventID

                          Year

                          Annual/Semiannual

                           

                          The first two tables, the "Song" and the "Singer", will be linked via the subEvent table, and will allow to create pairs "Singer" and "Song." Each Song+Singer pair will belong to a specific event.

                           

                          This is where I am stuck. Placing a field "EventID" into either the "Song" or the "Singer" table does not make sense to me because it is foreign to the content (i.e. event is not a feature of the song or the singer profile).

                           

                          Speaking about my background:

                           

                          1. I've been using FileMaker with flat DBs, and with two-three tables with pretty simple relations for a couple of years.

                          2. I have been through the "FileMaker 14 Pro Basic" book and its content poses no challenge for me. The same applies to the "Missing Manual"

                          3. Data types, simple calculations are not a big deal for me. I think I can handle a simple script as well.

                           

                          Sometimes I have trouble with how many tables I need. Even the examples in the books show that one needs to go over the DB and reduce redundant tables or merge them into one table.

                           

                          With the "Event management" solution, I need to be able, for example, after accumulating data from the recent 10-20 years to be answer the following questions:

                           

                          1. what songs were performed at the each annual event

                          2. who are the singers who performed at each annual event

                          3. what songs has each singer performed

                          4. for each songs, what singers performed it

                          5. keep ratings of each performance

                           

                          It seems to be a simple three-table structure but I am not exactly sure what I need to put into the layouts (the layouts could present data from more than one table) vs what I need to run via reports.

                           

                          I can put into the the "Event" layout the following fields

                           

                          EventIt

                          Year

                          Annual/Semiannual

                          SingerName

                          SingerLastName

                          SongName

                           

                          This layout will help with the data entry but how will I deal with answering all those five questions that I've mentioned above?

                           

                          This could be done either in the right and straightforward way or in an awkward and confusing way that would be hard to handle.

                           

                          Thank you for your feedback and for your help,

                           

                          Val

                          • 10. Re: Event management solution
                            vvkozmenko

                            Hi, morkus,

                             

                            Thank you for the reply. I understand what you are saying and I am agree with most of what you have said.

                             

                            With databases, like with everything in life, there re fundamental and applied types knowledge. The first one gives you a strong foundation and ability to solve any problem, and the second one that is that will give you enough skills to have accomplish a specific task. Another analogy is a short-term vs long-term benefits.

                             

                            Ideally, I would love to learn all database stuff. I think knowledge of how to effectively manage data shapes person's logic and ability to reason. I might do it in a slow motion because I have too many other things to do in a limited time.

                            At the same time, I have a task that I need or would like to accomplish, and I've picked FM because it seems to be a RAD solution.

                            Sometimes people get stuck on a simple thing that is confusing for them - and this simple thing could be pretty simple and obvious for more experienced ones. That's why I asked for help.

                            You have mentioned SQL. Would "SQL for Dummies" by Allan G. Taylor be a good start?

                            Thank you,

                            Val

                            • 11. Re: Event management solution
                              siplus

                              gimme 10 hours - it's 11:20 PM here - and you'll have a demo.

                              • 12. Re: Event management solution
                                erolst

                                vvkozmenko wrote:

                                1. I've been using FileMaker with flat DBs, and with two-three tables with pretty simple relations for a couple of years.

                                2. I have been through the "FileMaker 14 Pro Basic" book and its content poses no challenge for me. The same applies to the "Missing Manual"

                                3. Data types, simple calculations are not a big deal for me. I think I can handle a simple script as well.

                                 

                                Sometimes I have trouble with how many tables I need. Even the examples in the books show that one needs to go over the DB and reduce redundant tables or merge them into one table.

                                 

                                Once you think really hard about what entities you want to manage (and what relationships to have to each other), certain things will become clear(er):

                                 

                                There are …

                                 

                                Events – as a concept (name, timeframe) > Event

                                 

                                Events that are actually being held - the embodiment of a concept > EventInYear (?) EventAtDate (?) ActuallyOccurringEventAsOpposedToJustTheIdeaOfThatEvent (???)*

                                *You see how it can be hard to come up with a table name that is precise, yet not ridiculously long …


                                People who play certain roles in regard to certain entities – PeopleWhoPerformInASongAtARealEvent, or PeopleWhoWriteSongs – which gives you clues to other entities: People, written Songs, and performed Songs


                                So consider


                                Event (name) --< EventHeld (date) --< SongPerformedAtEvent (Performance) >-- Song --< SongWriter >-- People(_forSongs)


                                as well as


                                SongPerformedAtEvent --< SongPerformer >-- People(_forEvents)


                                which was a tad difficult to attach vertically to that line above …


                                vvkozmenko wrote:

                                1. what songs were performed at the each annual event

                                2. who are the singers who performed at each annual event

                                3. what songs has each singer performed

                                4. for each songs, what singers performed it

                                5. keep ratings of each performance

                                 

                                The nice about join tables – like SongPerformedAtEvent – is that they're usually able to answer all your questions (if you ask the right ones and do it nicely …), since they are – by necessity – related to all the other entities.

                                • 13. Re: Event management solution

                                  That's an excellent book.

                                   

                                  Since FM doesn't really support SQL in a mainstream way (only SELECT supported, etc.), I would focus on the database fundamentals portion of the book to grasp relationships between tables or entities-- that is, instead of spending lots of time learning SQL - unless you have time. SQL is extremely fast and powerful.

                                   

                                  -----

                                   

                                  The 1 to many is the most common (Customer:Orders, for example), but in your situation as I read it quickly, you seem to have at least one many-to-many relationship. As someone else here wrote, you will need a "join table" (also called a many-to-many resolver table), which is really nothing more than primary keys from both of the related tables.

                                   

                                  Say you have the classic "Teachers-Students". A teacher can have many students, but a student can have many teachers. A join table has the primary key from the Teachers table and the Students table, plus any fields that make sense at the join level like course grade, perhaps.

                                   

                                  So a M:M relationship is really just two 1:M relationships -- you only look at each of the 1:M relationships at any one time - like if you're trying to figure out which teachers a student has or which students a teacher has, for example.

                                   

                                  Check out Allan's book. It's a winner.

                                   

                                  If you want a good and inexpensive database diagramming tool, check out "SQL Editor" on the Mac. (Similar tools exist in Windows). It will generate the actual database for you for many database types, including FM.

                                   

                                  Hope this helps!

                                   

                                  - m

                                  • 14. Re: Event management solution
                                    vvkozmenko

                                    Hi, morkus,

                                     

                                    Thank you for the info - it really helps, as well as others' feedbacks!

                                     

                                    I just got myself a book - "Database design for mere mortals: A hands-on guide to relational database design" by Michael Hernandez. Hopefully it will bring some order to my chaos. Allan's book will be next.

                                     

                                    Yes, I am working on a Mac. Are you referring to a specific SQL Editor? I've checked App Store - there is a bunch of apps out there.

                                     

                                    Again, thank you,

                                     

                                    Val

                                    1 2 3 Previous Next