Tournament Stats

[mikajake]

Hi there..I have been given the daunting task of taking years of tournament scores and develop a database to calculate the bowlers tournament average. Each bowling season bowlers bowl in 3 mandatory tournaments and one optional. Each tournaments' scores and number of games (usually 6 or 10) are kept to calculate totals for that current season. Then they are purged at the end of the season. However, they are kept (archived) to calculate the bowlers true average of every game bowled. I think I need a tournaments, bowlers, scores table. But being a newbie at databases, I am not sure how to join them and how to keep the scores in the archive which I will populate with past data.

Any help would be appreciated.

P.s the current years results are rather tricky, but we will get into that later.

[timothybard]

The tournaments, bowlers and scores tables seem appropriate. To join them, you need a primary ID in each of those tables. The tournaments table would then have a field that refers to ID in the bowlers table (foreign key) and a field in the scores table that refers to the ID in the tournament table.

[mikajake]

Ok got ya, but how do i take say tourn 1 when finished and move the scores to a seperate table called averages that has the info from each tournament...name, zone, score, games etc?

Again thanks in advance

[timothybard]

Instead of creating a table to store the tournament results, I would recommend using a query to take the data in the tournaments and scores table to display the tournament results.

I'm not sure exactly what you mean by zone, score and games; however, if they are based on the data in the tournament and scores table, then you should be able to use a query to pull the information together and perform calculations.

If it is absolutely necessary to have this data in a separate table, then you can use a make table query to create a table from the data. If you give me more information about the fields that need to be in the averages table, then I can give you more assistance.

[mikajake]

I have supplied the actual data source that we use. Each year, I want to archive all three tournaments.

[timothybard]

The data you provided is very helpful. However, can you explain the meaning of the fields MJ, MJPOA, MJGAMES, MWB, MB and ZONE? Also can you explain the purging and archiving process?

Also, just so I am clear, the data you provided is for one year. I'm assuming you have a similar spreadsheet for other years. Is your goal to create a database that can store the data from all of these spreadsheets and perform calculations on all of the data, such as the average of the scores the bowler had over all of the tournaments, perhaps a total score, etc.?

[mikajake]

MJ, MB, MWB are tournament names. the fields with y,n in them are with respect to the bowler and are they bowling in that division. They are all in teaching division, but optionally in tournament and seniors. Yes the data is for one year. Purging is simple copy paste, delete. Yes that is my goal to consolidate all tournaments in the past. I have that data as well. The games fields are there because averages are calculated by total pinfall / total games. The odd time someone backs out of a tournament and does not bowl all 6 games. Zone is just a district ie, Thunder bay, Sudbury, Timmins etc..

Thanks again

Ps. one thing I should mention. current year totals are based on the two highest of the three tournments. Yes we drop the lowest. Then the two are the current years total pinfall. Standings are generated for the current this way. But, we keep all three for averages. Still with me...lol

[timothybard]

Are each of the tournaments the same each year? In other words, for the year you provided data, the three tournaments were MJ, MWB and MB; are those the tournaments that are done each year?

Based on the data you provided, here is how I would set up the database:

Bowler: ID (PK), Last Name, First Name, Sex, Zone, Active, Tourn, Teach, Senior
Tournament: ID (PK), Tournament Name
Scores: ID (PK), BowlerID (FK), TournamentID (FK), Year, Score, Number of Games, MJPOA (What does this stand for?)

As an example, here is how the first two records of the spreadsheet would be recorded:

Bowler: ID (PK), Last Name, First Name, Sex, Zone, Active, Tourn, Teach, Senior
1, AIKEN, ROB, MEN, NOS, Y, Y, Y, N
2, ALLAIRE, PAUL, MEN, GB, Y, Y, Y, N

Tournament: ID (PK), Tournament Name
1, MJ
2, MWB
3, MB

Scores: ID (PK), BowlerID (FK), TournamentID (FK), Year, Score, Number of Games, MJPOA
1, 1, 1, 2008(?), 1299, 6, -63
2, 1, 3, 2008(?), 1375, 6, 13
3, 2, 1, 2008(?), 1283, 6, 17
4, 2, 2, 2008(?), 1357, 6, 85
5, 2, 3, 2008(?), 1235, 6, -43

From this design, you can perform calculations such as average bowling score for each player over all tournaments, total score for each player over all tournaments, total number of tournaments each player played, average score of men vs women, etc.

I would highly recommend creating additional tables for normalization purposes, i.e.

Sex: ID (PK), Description (Male / Female)
Zone: ID (PK), Zone Name

You can then change the sex and zone fields in the bowler table to be foreign keys that refer to the above two tables.

[mikajake]

Wow! that is a lot of info you game me. Yes the three tournaments are the same each year. There is a tournament at the end of the year called the yearend tournament. I have that one set up as a flat file database that works. If it isn't broken, I won't fix it. Another time perhaps. I have supplied two result sheets for you. As for the POA..Pins Over Average. Each tournament, you have a new average so..if you have a 200 average and bowl 1276 (6gms) then 1276-1200 = +76.

Again thankyou very much

[timothybard]

Thank you for the results sheets. The database design that I mentioned in my last post can be used to generate those reports. The next steps would be to (1) create the database, (2) create an interface to input the data, (3) enter the data and (4) create the reports.

I didn't see any questions in your last post, so I wasn't sure if you needed any more assistance. Just let me know if you do.

[mikajake]

Sorry, I didn't know that. I don't want to bother you to much. Once I see how one database is done from a pros standpoint, then I think I can be on my way. I am assuming I should be using Access, filemaker, Lotus Approach etc... for this or is there something else? I have created the tables and created the relationships like so...Which table is the main database? I have lots of questions..so if you don't mind..

[mikajake]

updated relationship report with normalization

[timothybard]

First of all, you don't need to worry about bothering me.

As far as what to program the database in... I have experience with MS Access and mySQL with PHP. MS Access works well for databases that will be accessed through Windows based machines. mySQL with PHP works well if you need a database that is either accessable over the internet or accessible from multiple operating systems.

MS Access is a lot easier and faster to use from a programming point of view and that is what I use unless the database has to be accessible over the internet or from multiple OSs. Therefore, I recommend MS Access, but I can help you with mySQL with PHP as well.

I reviewed you PDF files. I did notice that you have both SEX and SEXID in the bowlers table. You only need one of those fields, not both. Everything else looks good.

As far as a main table... there's no official "main" table when it comes to relational databases; the important thing is the relationship between the tables. However, tables are usually used for 3 different purposes:
1) Data storage - These tables store the source data. An example of this is the bowlers table; it stores the name, sex and zone for the players.
2) Referential table - This tables are used for reference purposes. An example of this is the Sex table and the Zones table. They are used simply to show the different options for the sex and zone fields.
3) Relationship tables - These tables are used to show the relationships between two different tables. An example of this is the Scores table. It does hold data, so it is a data storage table, but it also shows how the bowlers are related to the tournaments.

Now, I just came up with those types myself, so I don't think you will find them in a book or anything, but when I think about my databases, I usually categorize each table into on of those three types.

Since the stat storage tables hold the actual data, those are the tables I start with when building a database or creating queries, but they aren't necessarily the "main" table. What is usually referred to as a Database is simply a collection of tables.

[mikajake]

I should do this in mysql / php. That way the higher ups with permissions could access the database and update it themselves. Plus the results would be accessable almost in real time. In terms of development time, which would be quicker. I am somewhat computer suave, just not in databases. So you can talk to me in not so laymen terms if you like. I have started to look at php / sql recently. I like it. I did some programming 20 yrs or so ago in bssic. I do understand most html and css. Also, i want to learn php..what a great way to do it.

If i do it in access i could apply security rules so that only the higher ups have access to certain parts of the program.

I am game for either one...just a matter of time. I could do it locally as I have wamp and upload after.

What about filemaker and its ability to do web stuff. Have you used it before?

[timothybard]

If time is a large concern, then I would recommend MS Access since it is a lot faster to program in. If you save the database in a directory shared over a local network, then multiple users can access the database at the same time.

If you need the database to run over the internet, I would recommend mySQL with php because they are supported by more web hosting companies than MS Access. I also find mySQL with php easier to set up than MS Access with, say, ASP, on my personal web server.

Lastly, my mySQL and php, you have to build the webpage, which includes how it looks; with MS Access, you drag text boxes, dropdown boxes, etc onto a form and it will look like a windows application.

Since this database is rather small as far as number of tables and fields, perhaps it would be a good project to do to learn php.

I think the three main factors are time, access (over a local network vs over the internet) and desire to learn php.

Personally, I use MS Access for all my databases unless it is a requirement that it works over the internet or by multiple operating systems.

Sorry, I have no experience with filemaker.

[mikajake]

They've waited this long, a little longer ain't going to hurt.

So Php / sql it is.

Should i get php writing software.