Unanswered: Database Design Suggestions for Sports History Program
been lurking around for awhile. good looking site. i have, and will continue, to learn a lot. look forward to chatting with everyone.
i have a specific question. its not so much a database techinical question as it is design. i am using access for my program, so I came here first.
a friend proposed a fun project that i decided to tackle. he wants a database driven college football history and statics program. ive decided to go with c# and have already determined the programming to be a cinch. the db design has proven rather tricky for me. for the first phase of the project he wants to be able to select a team and a year. from this he wants the program to display the teams conference, win lose record, points they scored and had scored against them that year, and a table breakdown of each game they played that year. so there will be somewhere around a dozen rows with the opponent, date, record of opponent, score, and a couple of other bits of info.
now for anyone who knows college ball, this is easily 10s of thousands of records. i dont know how many teams, 500? maybe and we have data going as far back as the 1860s. i have yet to come up with a very logical database design for this. my main problem right now is storing the conference of each team, which for many teams has changed multiple times over the years. UCLA is part of the "PAC10" conference today, but was in the "PCC" conference in the 1950s. Statistics based on conference would be a nifty feature to add to the program, so i really need a way to keep track of what teams are in what conferences, and the changes in conferences over the years.
My current design is weak at best, and doesnt at all account for the conference. I started out with the main table that lists all the teams, and assigns an AutoNumber that I use as the primary key. I figure then Ill need a table to store the game history, which like i said will right off the bat be thousands upon thousands of records, which i dont like, but see no good way around. i would have the date of the game, the AutoNumber generated key of each team, along with the info on that game. Just to get the info for one team for one year Im going to hit the database with dozens of queries (need to get the win lose record of each opponent which gets ugly too...)
anyway, like i said, programming the front end of this is a breeze, but im getting lost in the design. I apologize for a long winded post, which may not even be so clear, but id take any advice at this point. no suggestions could be worse then my ideas for this design!
First, may I suggest you go to my web site (see my signature block), then select the Access Tips link. You will find four articals there to read about normalization which will be a big help. Any database that has been normalized is MUCH easier to use than one that has not be normalized. So, you have done a very good thing here wanting to be sure that your database is well designed.
Here are a few suggestions toward this database design. From what I understand, there are about three things you want this database to provide. Team stats, individual stats, and conference stats.
So, you will need a conference table, team table, and an individual table. Each of these will only discribe each conference, team and individual. Nothing about dates, performance, etc yet. Just the bare, easy to see facts. Things like Name, nick name, date started, date ended, and became what conference. Team stuff about the same, and individual stuff about the same. Birthday, school attended, start date, end date. If anybody changed names, or schools changed names, then have a place for NewName, as in the Conference table. You might even want, NameCameFrom, so you can go both ways.
Then you will need the performance table(s). I would define the game once. DatePlayed, Schools involved, and maybe final score. From schools involved you can get the conference when used with the date the game was played. Then, you will need a lot of detail records to describe the game.
First table, Plays. PlayKey (autonumber), type of play, line of scrimage, length of gain(or loss), etc.
Second table, Players. PlayKey (foriegn key to table Plays), PlayerNumber (foreign key to IndividualTable), position, FunctionInPlay (carried, passed, caught, tackled, etc.)
I hope you can see the separation of information here. Keep the information close to where it really happened, and keep things that are not really related out of the low level picture. When you are describing the Play, or the Player in the play, who really cares what conference it was in? Yes, you will care about that later, but during the recording of the player carrying the ball for 4 yards, because you have the number of the record that describes the "overview" of the play, you can get the number of the game this play describes. Because you can get the number of the game, then you can obtain the two teams that played this game, then you can get the one or two conferences that were represented in this game.
thanks for the reply. for the first phase, we wont have anything about players or specific plays, but im sure that will be a feature that he'll want later if we can get the team part down. and he did mention a thing about coaches as well for later enhancements. this is all just for pleasure and learning, so there is nothing im against doing. plus i cant take my time since the next season doesnt start until august. ill take a look at your website in the morning and study it with interest. sounds like concepts ill definitely need to become more familiar with. thanks again