6 Replies Latest reply on Mar 7, 2012 8:08 AM by timwhisenant

    Need help designing a simple relational database please

    james_callery

      Hello,

       

      I'm creating a database for our sports tournaments where I'll probably have 3 tables. We have about 7 different tournaments, I call "Events", and each event can have many players and each player can play in many events. The problem I run into is the following:

       

      Since before I worked at my company, they basically used Filemaker as a glorified spreadsheet so they could print out mailing labels and create customized letters. We begin our "recruiting" process by adding a large list (over 200 players) to the database. They're basic information is entered into a number of fields, but for the most part all we have is their first, and last name, their school/team they play for, their school year, their position, and an address to mail them information to. Now, at this point, my company would create ONE database for EVERY event so they didn't mix up the players! From all the research and learning I've done about Filemaker, this method is primitive at best. After we mail out information about our events to these players, only a percentage send in the application. Once we receive their application, we do a find of the last name to see if we did in fact mail to them, and then update their information accordingly.

       

      What I would like to do is compile ALL of the event databases into one as to make reporting and summaries much, much easier. I would like the database to be able to keep records of ALL the kids we mail information to, as well as the kid who have sent in applications so I can make reports showing how many kids we have for Event A, B, etc...

       

      So far, I have 2 tables set up, one for Events and one for Players. In the Events table, my primary key will be the Event ID that stores information about each different event (cost, dates, directions, etc.). In the Players table, the Primary key will be Player ID, since each player is a unique record and we don't want to produce duplicates so I can avoid sending information to the same kid more than once. How do I go about connecting these tables so I can accomplish the reports I mentioned? I have an idea that I'll have to make a sort-of Line-items type of table that connects the 2, but I'm not too sure what/how to do.

       

      If anyone can offer any advice, I'd be extremely grateful! I've been working on this for months and I'm literally stuck in this quicksand of an issue. I'm trying to have it resolved by the end of March and I have no idea what to do.

       

      Thank you in advance!!

       

      Regards,

      James

        • 1. Re: Need help designing a simple relational database please
          timwhisenant

          Hi James,

           

          What you have is a many to many relationship. What that means is many kids can be involved  in many events. You need one more table to set between them to record the interaction of events and kids. It is often called a join table because it joins two tables together. It has its own key and a key field from each other table it joins and holds the fields that the join owns. This is the glue that says Johnny played in event A for the blue team, etc. or event B has the following kids involved, or Johnny was involved in the following events.

           

          Use a portal from either the events layout or the person layout to add to this table. When you setup the relationship you want to be sure the “allow creation of records in this table via this relationship” is checked on the side for the join table. Your reports should also be built from this table if they are participation based.

           

           

           

          Hope this Helps,

           

          Tim

          1 of 1 people found this helpful
          • 2. Re: Need help designing a simple relational database please
            sicoa

            Following on from Tim's comments:

             

            I would conceptually have three tables: Kids, Players, Events.

             

            Kids holds all information about the children i.e. Kids Primary Key, Name, Surname, etc.

             

            Events is a table with all your event info like Event Primary Key, Event Name, Event Date, Event Type, Event Location etc etc

             

            Players is a table with Kids that take part in a specific Event (this is what Tim refers to as a join table). Each record in this table holds the Player Primary Key, the foreign key of a Kid records and the foreign key of an Event record and then you can always add the player number, player penalties, player stats etc etc

             

            So what you then need to do is create the relationships between Kids->Players and Events->Players. Depending on your layouts and functionality, when setting the relationships, you will need to have the “Allow creation of records in this table...” option checked. You may also want to enable the "Delete related records in this table..." option to make sure you don't hold on to unrelated data.

             

            Good luck!

             

            Dimitris

            1 of 1 people found this helpful
            • 3. Re: Need help designing a simple relational database please
              james_callery

              Thank you for your response Tim. So this "join" table will be my main table from which I will do all my reporting? If that's the case, then can I use this table to enter all my new records from now on? For example, I have another 300 names to add to our database for people to mail to. Should I just add them via the Players table, or can I add them in this join table so I can tell my coworkers just to do everything they normally would so they don't get too confused.

               

              Also, in terms of relationships, do you think you could help me just set it up?

              The fields in the Events table are:

              Event (Primary key), Cost, Location, and Dates.

               

              The fields in the Players table are:

              Player ID (Primary key), Event, Cost, Deposit, Balance (calculate by Cost-deposit), Address (city, state, etc.), School Year, Position, and then a field denoting whether or not we've received an application from them.

               

              Now, if I were to create this join table, what would it look like? Where can I add a field to reflect what team the player is on in the event?

               

              Again, any help you can give me is appreciated beyond belief! You've already given me a lot of help.

               

              Thanks Tim!

              • 4. Re: Need help designing a simple relational database please
                timwhisenant

                James,

                 

                I will try to get you a sample file today for you to see the join table in action.

                 

                 

                 

                Tim

                • 6. Re: Need help designing a simple relational database please
                  timwhisenant

                  James,

                   

                  Here is the file for you. It is not really polished but I hope it will give you the idea.

                   

                  Questions?? Just ask.

                   

                  Tim