Sports Statistics

[Dchall_San_Antonio]

Every now and then I need to stop by here and show my ignorance of basic database workings.

One of my hobbies is statistics. Now that my daughter is playing volleyball, I thought I'd set up a place to track performance. My comfort zone is Excel and that has worked very well, but I know spreadsheets are fraught with peril. As I started implementing my spreadsheet in FMP I eventually realized that I goofed in the basic set up. I put all the data values into the "personnel" database. That works great for 1 event, but I obviously will need to put data in for many matches. Each match has 2 or 3 sets with data for each set grouping into a match. Then I would also need to be able to group matches for season statistics. And I would need to separate regular season play from tournament play and accumulate statistics for each or all.

From reading the forum, I found a post on a similar issue but with different circumstances. Still it would appear that I could fix this with a db for the players, one for the events, and a db that joins the other two and holds the performance records. I started to set that up around the db I originally started with (goofed up). At this point I think I have most of the pieces but not in the right places. I'm not sure how to fix this. Any suggestions?

VolleyballStats.zip

[Matthew F]

I think you're on the right track. I'd suggest pulling out the player info into a separate team roster so you don't need to enter it each time.

VolleyballStats.fp7.zip

[Dchall_San_Antonio]

mfero,

Thanks. Couple more questions...

1. I see you have added the Player Roster, Teams, and the Player match data db's and related them to the PerformanceData and Matches db's. How did you pull the fields out of one db and put them in another? Please tell me it was by the drag and drop method and not by the brute force and ignorance method (the one I would have used). I spent considerable time yesterday trying to figure out if that could be done easily. Or did you duplicate the db and then delete the inappropriate fields from each one?

2. I'm still not sure how to make the records and fields work. One of my layouts is titled, "This is how it should look for 1 record." The way I wrote it (and the way you continued with it), each player is a different record on that layout. That's what I am trying to get away from. In my vision of how this should work, each event should be 1 record with all the players and data as fields, not records. Should I create a new layout (List/Report format) and bring in the players and the Player Match Data fields for each player? Can I bring in the entire team and data fields at once? I like the spreadsheet user interface and want to be able to input the data on the form that looks like "This is how it should look for 1 record."

[Matthew F]

How did you pull the fields out of one db and put them in another?

Under the 'Tables' tab of 'manage databases' I copied and pasted your players table to duplicate it, instead of making a new table from scratch. I then deleted the overlapping fields from the two tables.

[Matthew F]

Its good that you have a vision of how you would like it to look, but I'm not quite sure what you're after. One thing you should consider is what sort of raw data you are going to have for data entry. You will want to design a layout that makes this task very easy, and perhaps design other layouts which summarize or report the data nicely.

each event should be 1 record with all the players and data as fields, not records

Perhaps you're thinking too much like an Excel spreadsheet where recurring data can be presented either in rows or columns. In Filemaker, I prefer use new records (rows) rather than new fields (columns) for recurring data. With the exception of the Table view (which is more like an Excel sheet), its hard for the end user to generate new fields. Also, fields can not be deleted by the user. If you want to use a portal, you will not be able to add new players without going into layout mode and redrawing the portal. So a major paradigm difference, between Excel and Filemaker, is that in Filemaker the location of Field is completely free-form. The downside is that 'row' summary statistics are harder to generate.

[Dchall_San_Antonio]

Thank you for your patience. I have been using Excel since it came out on the Mac in the 80's and consider myself to be far beyond the average user in spreadsheet skills. Every time I see a problem I can see the end result as a spreadsheet. Interestingly I cannot envision a db design to save my life. 100% mental block.

Thanks for the previous verification of how you created the second db for the team roster. I've done the same - pretty simple once you get over the initial mental block.

I am absolutely thinking like a spreadsheet! Forgive me for one last minute for slipping into spreadsheet language, but that is the only thing I really understand. The way I did this in Excel was to make one worksheet with the player names and stats exactly as you see it in the db (coincidence? not hardly). That worksheet works for one match. For the next match, I make a copy of that worksheet and erase all the stats in the copy. Then I type in the new numbers. The only thing I have to type is the numbers. Then I created another (identical looking) worksheet that totals the data from all the match worksheets and gives season performance stats. So in summary each Excel worksheet is a record of all the performance data for each player for one match.

Now I have to stop thinking like this is a spreadsheet. That's how I got into this mess. I've heard of portals and "successfully" avoided learning anything about them so far.

I have another FMP db I use for invoicing clients. Each day is a new record of expenses. There are two db's: one of administrative details (client and billing stuff) and a second with all the expense data. I'm looking at that to see how I designed it (4 years ago) to work. I think I see the analogy if I can just see the theory.

[comment]

The way I did this in Excel was to make one worksheet with the player names and stats exactly as you see it in the db (coincidence? not hardly). That worksheet works for one match. For the next match, I make a copy of that worksheet and erase all the stats in the copy. Then I type in the new numbers. The only thing I have to type is the numbers. Then I created another (identical looking) worksheet that totals the data from all the match worksheets and gives season performance stats.

That's not much different from how you would do this in a relational database. I assume by "season performance stats" you mean stats of a player - not of a team? The Players to Teams relationship is still not quite clear. Can players switch teams mid-season?

[Dchall_San_Antonio]

Players can switch teams mid season. There are three teams, A, B, and C, from the same grade. The A team has the most skilled/talented/experienced players. C has the least. B is in the middle. Teams are selected originally so that they all might have a winning season. Thus the A team has most of the good players but some of the less talented or inexperienced players are assigned to A. Team C has most of the untalented and inexperienced players with a couple of relatively skilled players. B is in the middle with a mix of some very good and some who show a lot of promise. If a player on one team gets injured, then the team rosters shift around.

That's not much different from how you would do this in a relational database.

Whew! I might be able to do this after all.

Here's a new question. Should I have a new db for every player in which to store the performance stats? Relate it to the others with jersey # and team name?

[comment]

Should I have a new db for every player in which to store the performance stats?

No, of course not. Let's look at this "one worksheet with the player names and stats": assuming that each player is a row with the stats being columns, let's call each row a record in a table. Which table?

You say that this worksheet "works for one match". But if you had another column to indicate the match, it could work for ALL matches just as well, couldn't it? And since we don't want to duplicate data, let's just have an ID for each player and use that.

So now our stats table would have these columns (= fields)

PlayerID

MatchID

TeamID (optional, see more below)

Ace

Attack

AttackError

...

If we want to know which player this record belongs to, we look into the Players table where all the player's details are. Same with the matches - all the match details would be in the Matches table, and we can look at them through a relationship based on MatchID.

Players can switch teams mid season.

OK, then the next question is how closely do you need to track that. If you want to keep the stats associated not only with the player, but also with the player's team at that time, then you would need to record that as well. This way the stats table could also be a roster table (provided you record all players, not just the ones who have stats to enter).

[Dchall_San_Antonio]

Thanks again. That short list, and a lot of fiddling around, got me going. This is working out great!

[Dchall_San_Antonio]

Hello, I'm back with more issues. At this point I'm tuning it up, though. Still I am missing a fundamental link so this question still goes into the relational db forum.

1. In the TeamRoster db and layout, I created a PlayerID number consisting of the concatenation of the player's jersey number and team name. It is a text field. Now when I search on that field for "2 - Blue", rather than getting only jersey number 2 on the blue team, I get all the jersey numbers from the blue team that contain a 2. Also when I sort the Player ID field, it sorts like a text field instead of a number field. I know I can fix that with leading zeros on the jersey number, but is there another way to create a unique PlayerID number that would be easy to recognize?

2. Switching to the layout titled, Individual Stats, when I do a Find on the Player Name field, it works except when there are duplicate jersey numbers on different teams. If I search on a player on one team who happens to have a jersey number which is duplicated on another team, I get the stats for both players. I've fiddled around and can't seem to get it to recognize the name or PlayerID number instead of the jersey number.

VolleyballStats_-_2_Copy.zip