Normalization Assistance for Tables Please??

[webmaniac]

hello all...

I am creating a high school sports database that will be published as ASP pages...i am having difficulty trying to think of how to set up the tables for normalization purposes

when the visitor interfaces with the site, they will

Select Season (2006, 2007, etc.)
Select Sport (Football, Basketball, Volleyball, etc.)
Select Team (Cardinals, Bulldogs, etc.)

and view by team

Date of Game Selected Team Opponent Score Win or Loss
9-1 Cardinals Bulldogs 14-10 W

this means, however, that when the inverse team is selected it would appear as

9-1 Bulldogs Cardinals 14-10 L

When the team is selected, it is great if the visitor sees all of the scheduled games and the score and win appears when the database is updated following the game

I just cannot seem to figure out the normalization for this: I have tables:



Season
Sport
Teams
Opponents
Games
Score

Does this table set up seem even close? and how do I report the games by team so that a team may appear as selected team or as selected team's opponent???

Thank you to anyone with any suggestions or direction on this!!!

[DanInManchester]

This is how I thought it would be :

tbl_Seasons
SeasonID (PK)
Year

tbl_Teams
TeamId (PK)
SportId (FK) (optional*)
Name

tbl_Sport
SportID (PK)
Name

tbl_Games
GameId (PK)
HomeTeamId (FK)
HomeScore
AwayTeamId (FK)
AwayScore
SportId (FK)
SeasonId (FK)
GameDate


* You may want to associate a team directly with a sport so that when you set them up you can specify which sport that particlar team plays. then when someone comes to setup a game you can ensure they are not trying to create a game between say a football team and tennis team (if that makes sense)

I wasn't to sure of the purpose of the oponents and score table existing on their own.

[matthewspatrick]

You could get really normalized. This might be overkill:

tblSchools
--------------------------------------------------------------
SchoolID (PK)
SchoolName
<other school attributes>

tblSports
--------------------------------------------------------------
SportID (PK)
SportName
<other attributes>

tblLevel (i.e., varsity, jv, freshmen)
--------------------------------------------------------------
LevelID (PK)
LevelName

tblTeams
--------------------------------------------------------------
TeamID (PK)
SchoolID (FK)
SportID (FK)
LevelID (FK)

tblAcadYears (stands in for seasons; assumes 1 season per AY)
--------------------------------------------------------------
AcadYear (PK)

tblGames
--------------------------------------------------------------
GameID (PK)
GameDate

tblGameTeams
--------------------------------------------------------------
GameID (PK, FK)
TeamID (PK, FK)
Score
IsHomeTeam


I would imagine you'd want leagues brought into this somehow, too...

[webmaniac]

I want to thank you guys VERY much! I was just completely blanking on this and beating my head up against a wall. Thank you very much!!!!

[webmaniac]

matthewspatrick,

i am a little confused with the primary keys....tbl_GamesTeams has two primary keys which I figured is a mistake so I played with it but neither of the two can be a primary key in that table because game id= games played on a date and that would be many on the same date and teamid = teams that played on that date and the teams would have to appear more than once because they would play on different days???

[matthewspatrick]

matthewspatrick,

i am a little confused with the primary keys....tbl_GamesTeams has two primary keys which I figured is a mistake so I played with it but neither of the two can be a primary key in that table because game id= games played on a date and that would be many on the same date and teamid = teams that played on that date and the teams would have to appear more than once because they would play on different days???

It was no mistake. Access allows you to define a compound primary key; in this case, you need two fields to identify a unique entry in the table.

The idea is that for any given game, you have one entry in tblGames, and each participating team in a game (or meet, for that matter, if you look at swimming, track, or wrestling) has one record in tblGameTeams.

Thus, for a basketball game, you have one record in tblGames, and then two records in tblGameTeams (one for each team). It may look complicated, but it actually makes querying easier later on.

[webmaniac]

great! thanks...i'll play with that

[webmaniac]

i'm just wondering as i enter the data in the tables if i am doing something wrong because i am having to sit here with a chart for each team

Yahoo Cardinals =
sport id 1
school id 1
teamid 1

Yahoo Freaks =
sport id 1
school id 2
team id 2

etc.

to enter the data correctly....am i on the right path or am i missing something

[matthewspatrick]

Without seeing more of your data, it's hard to say.

Of course, there is more than one way to do it. I showed you one way, but that is not to say that it is the best way possible.

[webmaniac]

no no i am going wtih you lol it looks like its gonna be great for retrieving data just a little confusing to enter it but that's ok...i would rather take the time to enter data that is going to work....

thank you again!!

[webmaniac]

matthewspatrick,

I have some difficulty with my relationships - when I try to query, the query functions in that it doesnt collapse the rows and lock the query but I don't get any data. I think my relationships are affecting the assignment of the team name to the tbl_GamesTeams team ID maybe.
I am trying to upload a picture of my table relationships to see if they look correct to you?