need opinion on table structure for bowling db

[DJBummy]

I have attached a word doc showing my table relationships. I would like an opinion on as to whether it is normalized correctly. It is for a bowling league that has 10 teams and 50 bowlers. The team names, lane assignments will change every year and the bowlers will be on a different team from season to season.
Also the tblScores is set the way it is because some bowlers will show up late or injure themselves so because of the awards that are handed out at the end of the year for high scores, perfect attendance etc. this is how I thought it would work best.
I wanted to make sure before I put all the bells and whistles in. Thanks in advance.

D.J.

 

[statsman]

For many years I have been doing the stats for a number of sports leagues. I have not attempted bowling however.
Essentially my database consists of only two tables: Players and Teams.

All of the data concerning player stats is run from the table Players.
All of the data concerning team stats is run from Teams.

When updating Players, I use a form run from a query sorted by team. This allows you to update directly from the score sheets. Things like attendance is easy. If there are 20 matches in a season and the player has 20 entries, he made it to all the matches.

I also include the year of the season in both the player and team tables.

I have a totally diferent database called Archive. At the end of each season I use an append query to update the Archive. I then delete all of the data in the original database and I'm ready for next season.

 

[DJBummy]

Thank you for your response.
I thought about doing what you suggested. Deleting the info every year and starting over. Correct me if im wrong but it seemed to me that was breaking the rules of normalization. (avoiding many to many relationships, duplicating data entry, etc.).I did look into the different examples of sports db's here. Soccer, Baseball, football etc. The examples just didn't seem to work right with bowling.
I have been trying to teach myself access for the last 5 years or so and little by little i have been making progress. At times I will post here to make sure that I am doing things the proper way or if I get stuck on a particular problem.
From reading the many posts here I got the impression that normalization of the database is the most important first step to database design. Hence my concern for my table structure and relationships.
Once again thanks.

 

[statsman]

My basic thoughts have been the following in Access for many years.

Tables - contains raw data only

Forms - use for data entry (a lot of people like to display their data in forms, I usually use reports for that and the user can print it if he wants)

Queries - manipulate the data in the tables. For example get the total points scored from the games won, matches played, allstars etc. All of these are calculated in the query from the raw data in the table. So only enter data into the table that you will need to get the numbers you want.

Reports - as above

At one time I tried to keep all of the data from all of the seasons in 1 database. It got so large it became very slow. Plus if you have data for several seasons in the tables, you have to specify which season everytime you want to do somthing. I find the current season in 1 database, past seasons in a second works very well.

Good luck on your endevour and may all your spares not be 7-10 splits.

 

[DJBummy]

Thank you for taking the time to respond

I guess my next question is this. Is it common practice to start a new database by emptying tables?

I think what you are saying is that in my case to empty the scores table and start a new season by copying and pasting a new database? Is this right?

I think I know what your are saying. For instance why would I want to know what someone bowled last year?. There are a couple of reasons. One is if the bowler participates in a tournament his average is based on his average from last years season. To me I should be able to just click on a button to retrieve that information.

I have created a form to retrieve different seasons by having a form pop up on start up where the user chooses a season from a combo box and the form is then hidden until the user decides to switch to another season. So far this works as I would like.

My problem is this. I was raised in a Marine Corp family and when my father wanted me to do something if it wasn't done right the first time I had to do it again. Thus my obsession with procedure.

I have enjoyed my learning experience with access and have found this sight to be most valuable and infomative . Thanks to such users as yourself.
Thanks.

 

[Kassy]

Seasons!

On looking through the postings- read yours and found it interesting, like you I have been trying to develop a database but mine is for rugby members. The bit that caught my eye was the Seasons. I have 'dithered' about creating a table for seasons and how to go about it because I would like to select all members for a particular season. Eventually with a LOT of help from these forums I managed to get a function to select a season based on payment dates, which I use. Because the database was already established before I took over there were past years and lots of information is sketchy to say the least. Making a season table would have have probably saved me loads of hastle however wasnt sure about what way to go about setting it up. have you any advice?

 

[statsman]

Perhaps I didn't explain well.

The first database is for the current season only. All of the data is copyed into the Archive database at the end of the season. So, at the end of the current season you have all of the team and player data available since you started saving the data. As this information is on file in the Archive database and saved by year, you now longer need it in the current years database. Once the season is over, it no longer current, its Archive.

All of the necessary stats for the current season are kept in the current season database. All reports etc. for the current year are run from there.

All of the Archive information (example, how well has a specific player done over the past 5 seasons) would be run from the Archive database.
*******************
I Trust I Make Myself Obscure - Thomas More - A Man For All Seasons