Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2004-02-01 : 13:23:07
|
| Hello-I'm planning on building a small project just for fun. I had some ideas how to set it all up, but I figured I'd ask the experts for their opinions before I make any hard decisions.I'd like to create a small website that tracks the RPI (Rating Percentage Index) of all NCAA Division-1 basketball teams. Yes, I'm well aware there are already sites that do this (kenpom.com and collegerpi.com to name a couple), but I'd like to add a few additional features that those sites don't offer. To begin though I'd like to start by just calculating the RPI.The RPI calculation is a failry simple one. To calculate a teams RPI you do the following.(Take the Teams Winning Percentage * .25) + (Take the Teams Opponents Winning Percentage * .5) + (Take the Teams Opponents-Opponents Winning Percentage * .25) As you can see this is pretty simple, but fails to take into account things like home court advantage among other variables.There you have it. So my first dilemma is how to store the information in the database. My initial thought was to have a table with two columns. A primary key identity column, and a column with all of the teams. 2nd, I would have a table with five columns. The would be Date Game Played, Team 1, Team 1 Score, Team 2, Team 2 score. This is where my questions start. Should I also have a column where I give the team that won the game? I know it can be derived from the score, but would my calculations in the end be easier if I had it defined?I will probably also add a column that defines if the game was a home game, netural court, or away game for Team 1. This wont be used for the RPI, but might be used down the road for some of my improved features (among which would be determining the home court advantage each team has).Does anyone have any input on this? I'd rather get it right the first time and not have to work with a badly designed database.Thanks for any input-Nick |
|
|
sdctechnology
Starting Member
1 Post |
Posted - 2004-02-01 : 19:47:01
|
| Hi Nick,I'd leave the proposed sixth column - the winning team - out of the second table. It's a simple enough logical test to determine the winner code-wise and hence a pretty serious data redundancy.-=Huzzah for the shopkeep!=- huzzah! |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-01 : 20:02:35
|
| I've just go to ask (at the risk of starting a flame war).. Are Teams name unique? My bet is they are and the identity column is redundant and just adds an uncessary join. I'd also go with sdctechnology recommendation and not store the winner. Just create a view that derives it.DavidM"SQL-3 is an abomination.." |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-02-02 : 09:41:36
|
| More than likely team names are not unique. School names maybe (there are like 300 Div I basketball schools), but not Team Names ie. Clemson Tigers and Auburn Tigers. You could combine them for a PK. |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2004-02-02 : 10:42:01
|
| I searched back and found another topic that disussed storing games/matches in a db.Someone suggested that there be a team table, a match table, and then a team/match table. Basically every game would have two entries in the team/match table.Now this makes sense from a normalization point of view, but when it comes right down to it, do I want perfect normalization or something that is a little denormalized but easier to use?And I'll just be storing the school name, so the identity column probably is redundant. I'll be removing that. |
|
|
|
|
|
|
|
|
NCAA Record Database Design