I’m having great difficulty getting a database setup to handle a large amount of information.
It’s for an 8-ball league that my wife and I run. Every week there are a dozen matches. Each match is two teams, 5 to 7 players per team and 35 games. We track individual player game scores, averages, handicaps, runouts, and other information as well. Each scoresheet has roughly 350 bits of information. Twelve scoresheets per week and twenty-seven weeks in the season. That works out to around 113,000 bits of data.
My initial attempt had 364 records and 348 fields. It works but slowly.
Then on my next attempt I thought I would try to simplify things by reducing the number of fields down to 7 and putting a lot of the needed information into a dictionary for each record. Each dictionary has 343 entries. I expected an improvement in performance with the reduced field count but it is actually considerably worse.
These attempts take a lot of time to put together, and I don’t really want to waste too much time with poorly-conceived structures.
Do you guys have any suggestions for a general structure for my database?
How many teams in total - 24? How many players in total? I think you’re saying that each match is the best of 35 games, is that correct?
Can you post a scan of a score sheet?
My initial thought is that you should have a separate database for each team and that a central database would coordinate data entry into each one and the collation of date for whatever final stats you want to produce but I need to know more about how your system works.
Michael,
It’s 7 rounds of 5 games. Each team has 5 players and up to 2 spares that can go in at random places. We play all 35 games in a Tuesday night. So for every player I have games won, games lost, points made (0-27 every game), runouts for, break-and-runouts against (BROA), a correction factor for BROAs. For cumulative stats we also have total games won, total games played, total points made, total runouts for, total BROAs against, game percentage, handicap, improvement over last week, improvement over last year. Lots of details to track!
The obvious question is - if your current system works, why change it? Is it dreadfully slow, like taking hours to run or is it just not instantaneous?
That said, I’m sure there is scope to make it much more efficient. Your system could pre-print all scoresheets with dates, players’ names and handicaps included and you could have a matching Panorama X form into which the scores are entered - or you could have a laptop on site and enter the scores directly with the hard copy as a backup. The system could then do all the necessary calculations based on scoresheet data. Maybe you already do this?
I’ll continue this discussion via personal messaging rather than clutter up the forum with lots of traffic but I’m sure Larry would welcome any other input.
It seems that it would be better to have several files related: Teams, Players, Games, and Schedules seem to be the very least. Each Game would have statistics that could be stored into the Teams and Players files.
I do the scheduling and rosters for a bocce league, and the rosters are kept separately from the schedules. The schedules never change. The roster is the list of players, and they are assigned to teams. If I wanted to keep statistics, I would relate the two through a third file, but there is not much point to statistics in bocce.
I had everything in ONE FileMaker Pro database for the last 5 years or so. Too expensive and too inflexible for what I want it to do. I have been working on this in PanX since April of this year. I have probably well over 500 hours into it. I do enjoy the programming very much though. I have it set up with six databases: Control; Schedule; ScoreSheets Teams; Players; and PDF_Maker. It works but the ScoreSheets database is kinda sluggish.
I did some sleuthing on this forum about large and slow databases, and then did some experimentation with different things. Huge improvements in speed when I hid most of the fields in the ScoreSheets db. It moves quite nicely now, even at 62Mb.
Thank you all for your input and suggestions.
Well, I had to laugh at myself because I couldn’t figure out what game Larry was talking about until he posted the score sheet for eight ball pool. I haven’t played pool in years but you are correct in that there are a lot of details to keep track of. One of the reasons is that there are so many ways a round can end.
I think you have the overall structure figured out - just keep improving what you have already. If you can identify specific procedures that are sluggish, I suggest that you post them here and ask for help in speeding them up. The Forum members are great at offering suggestions when given a piece of code.
