1 2 3 Previous Next 108 Replies Latest reply on Jan 25, 2013 9:32 PM by philmodjunk

    Database setup

    MikeProcopio

      Title

      Database setup

      Post

           Starting a new database to run basketball camps. The main table will be the player table to put the information in on the campers that will be attending camps. 

           The Next table will be high schools. Since I'll be dealing witht he same high schools all of the time I want to enter the information like address, coach, coach phone number etc.

           There are different events that I run SHowcases, camps, and tournaments each will have their own table and a participation table in between the player table and event table.

           For  the showcases and camps I use basically the same staff so I will have a staff table to connect to showcases and camps.  Each Player plays on a club team on top of their high school so I will have a club table. I want the club table to connect to the player table and tournament table so I can connect club teams to the tournaments that I run that is a tricky one for me. 

           So here is what I think my table will look like

            

            

           Player  <_pkPlayerID-----------_fkPlayerID> High School

            

                        <_pkPlayerID-----------_fkPlayer ID>Club

            

                        <_pkPlayerID-----------_fkPlayerID>Camp Part<_fkCampID---Camp----_pkCampID>

            

                       <_pkPlayerID-----------_fkPlayerID>Showcase Part<_fkShowID ----Show-----_pkShowID>

            

                       <_pkPlayerID-----------_fkPlayerID>Tourn. Part<fk TournID---Tourn----_pkTournID>

            

           Ok so I want to have Club that is connected to the player to connect to Tournament. Also I assume that I can have a staff table with a staff participation connectied to Showcase and Camp but dont know how to connect one table to two different tables in some situations it wants me to create a seperate occurance. Any suggestions?

        • 1. Re: Database setup
          philmodjunk

               Player  <_pkPlayerID-----------_fkPlayerID> High School

               I think that should be:

               Player::_fkHighSchoolID = Highschool::__pkHighSchoolID

               Otherwise you will only be able to have one player from each highschool in your database.

               The next part looks functional, but I'd spend some time carefully answering this question: Could I use one table for Camps, Showcases AND Tournaments? This may make some forms of reporting easier and there is even a compromise approach that links a single Participation join table to a single "event" table, but then you link in three different "detail" tables for each of the three types of events. Please note that I am not trying to recommend for any of these three approaches, but am suggesting that you consider all three carefully and keep all three in mind as you move forward in your design as sometimes it does not become clear which approach is optimum with the first draft of your database design.

               Linking in a club table should be fairly simple if a player is only ever a member of a single club team:

               Clubs::__pkClubID = Player::_fkClubID

               Then, when you link a player to a given "camp" event, you also link the relevant data from the club table.

               If it's even remotely possible that a player could be linked to two club teams, you'd need a Join table. Also, if you treated clubs and Highschools as two different type of "team organizations" in the same table, you'd need a join table to link the players' highschool and club information.

               Staff participation will need to be linked in with a join table (participation) just like you have with players since I would assume that you have multiple staff at each event and that each staff member participates in more than one such event. And yes, if you keep separate tables (or table occurrences) for each type of camp event in your relationships graph, you'll need multiple occurrences of your staff table.

                

          • 2. Re: Database setup
            MikeProcopio

                 You are right about the high school, totally fell asleep. You showed me that one a long time ago and forgot to switch it around. 

                 I think I am going to put a hold on running the Tournament table, but will have the club team table to link players to. The Staff and Staff Participation will be easy there. My question with the staff would have been if camp and showcase would have been separate, but since I will keep them in one table I think that will be fine.

                 Players can play on two different club teams. They play on the same team but different age bracket. If a 15 year old player is very good they can play on a 15 year old team and a 17 year old team. So If I had a club participation table to join them then I think I'd be ok with it. Can I make a team directory when using a participation table? On another solution I connected a player with the club table directly and was able to make a directory that listed the coach and all players that played on this team. Just wondering if I could do the same using a participation table.

                 For each event (Camp/Showcase) I want to be able to give each player a jersey number, put them on a team, be able to access rosters of each team and what coach is coaching them. Also I would like to be able to have how much each event costs, and how much each players owe after payments/deposts etc.

                 Thanks Phil

            • 3. Re: Database setup
              philmodjunk
                   

                        If a 15 year old player is very good they can play on a 15 year old team and a 17 year old team.

                   Wonder if down the road, you might have data from previous years in your database? If so, then you might also have the players club team membership recorded from previous years.

                   

                        Can I make a team directory when using a participation table?

                   Yes, this is the same type of situation. It's just a matter of using a find or GTRR to pull up a found set of just the members of that one team.

              • 4. Re: Database setup
                MikeProcopio

                     Phil,

                     I had a question. On another solution I have a high school schedule table that gives the schedule of a team where you can enter game date and times etc. On the team table there is a portal with the games listed. When the season is over is it better to delete all of the records and just re enter them, or on the schedlue table put a seaon field and just Filter the portal on the school table for the current season

                • 5. Re: Database setup
                  philmodjunk

                       Generally speaking, deleting data should never be done without checking and double checking your reasons for doing so. Once you've deleted, the only way you can get that data back is to open up a back up copy, if one exists, made before the data was deleted.

                       Thus, my initial response is that it's bettter just to filter the portal (or the portal's relationship) to exclude data from past seasons. That way the data is still there should you ever need to go back and look at what is now the historical record of past schedules.

                       But that assumes that there is some conceivable use for that past data. And if you are running around with the DB on an iOS device, starting fresh each season might help keep down the file's size to better "fit" on your iPad or iPhone...

                  • 6. Re: Database setup
                    MikeProcopio

                         Yeah you are right, I tried adding a season to the schedule and just filtered the portal it worked like a charm. 

                    • 7. Re: Database setup
                      MikeProcopio

                           Phil,

                           OK so I will have to have a tournamnet table after all. I think I will combine camp and showcase as they are basically the same thing. I think tournament will have its own table because now teams will be charged and not individuals. For tournaments I will need to be able to print rosters of all the participating teams for each event. So would I have for my relationship

                           <Player> _pkPlayerID----------_fk PlayerID>Club Participation<_fk------Club ID---------_pk CLub ID------><CLub>

                            

                           For the tournaments I would want to charge each team a fee instead of each player. I'd like to track all tournaments that each player participated in. If you think we can get away will having just one particiaption for all three and it will be fairly easy to use I am open to it.

                      • 8. Re: Database setup
                        philmodjunk
                             

                                  think tournament will have its own table because now teams will be charged and not individuals.

                             That detail by itself would not convince me to put tournaments in a different table. There are ways to handle the billing differences without using a physically distinct table for tournament events.

                             Warning, billing and payments will require additional tables and relationships and their structure will be determined by your business practices with regards to payment. If you require payment in full from the club on the day of the tournament, this is fairly simple. If they can pay in advance with a single payment that pays for more than one tournament or can pay in installments, the structure is more complex.

                        • 9. Re: Database setup
                          MikeProcopio

                               OK Cool I'll keep all one. 

                               Couple of questions.

                               When it comes to showcases or camps it will be individual kids in the events which is easy to do, but for tournaments I want to put full teams into the tournaments. Is there a way to do that for full teams, or do I just put a search for players that play for a certain team and then enter them all in the event of a replace records selection.

                               The Money part will be a slow process and I am not worried about that right about now.

                               I am going to have a high school table as well. Some tournaments could be for high school teams as well. Is it possible to connect high schools to the events as well? 

                               The secondbiggest thing in this will printing layouts like rosters of teams and things like what rooms kids are staying in during camps, staff lists , but I think thse things can be done with little issue.

                          • 10. Re: Database setup
                            philmodjunk

                                 When it comes to showcases or camps it will be individual kids in the events which is easy to do, but for tournaments I want to put full teams into the tournaments. Is there a way to do that for full teams, or do I just put a search for players that play for a certain team and then enter them all in the event of a replace records selection.

                                 You could do it either way, but entering the entire team roster one player at a time would be a crazy amount of unecessary handwork. It's a wrinkle I hadn't thought about yet, but I think you can make this work by linking clubs instead of teams by using a separate set of table occurrences with their own set of layouts customized for tournament management. Thus you have different table occurrences and relationships, but the same data source tables.

                                 

                                      Is it possible to connect high schools to the events as well?

                                 It might force the addition of another join table so that you can link a player to both a club and a school, but have you considered putting high schools and clubs in the same table? From my perspective, it sounds like theres a lot of data that you would record for a club or a highschool in exactly the same way...

                            • 11. Re: Database setup
                              MikeProcopio

                                   Phil,

                                    

                                   When you say different set of table occurences do you mean new tables or do I copy the club table and then make another occurence off of that? 

                                   As I explained I have the<Player table>--------<Event Participation>--------<Event>---------<Staff Participation>-------<Staff>

                                                                                <Playe Table>--------<High School>

                                                                                 <Player Table>------<Club Team>

                                   How would I start and where for the join table?

                              • 12. Re: Database setup
                                MikeProcopio

                                     Sorry Phil forgot to explain further, I want to join the club team and high school to events. You told me to create new layouts for just High School,Camp, and Club Team events after they are joined. I will deal with the layouts after the tables are joined.

                                • 13. Re: Database setup
                                  philmodjunk

                                       I meant new occurrences of the same tables, but linked in a different set of relationships to support the differences in how these events need to be managed. I mentioned new layouts because you'll likely need them to be based on these new table occurrences and the different relationships are likely to drive different optimum layout designs.

                                  • 14. Re: Database setup
                                    MikeProcopio

                                         Do you have any ideas on what the relationship be? I'm struggling with ideas.

                                    1 2 3 Previous Next