Table Set Up Assistance Please

[webmaniacal]

hello...I am really blanking on this with regard to normalization and how I need to display the information. I am creating a sports database that will be displayed in a selectable ASP page ... I need the visitor to a web site to select and see

Season "2006"
Sport "Football"
Team "Cardinals" and "Other Team" Score Win, etc.

My question is how do I set up the tables so that the visitor can select by team and see that team and the opponents they play because in a table the same team is sometimes the team they select and sometimes the opponent

would you enter the information both ways? i'm confused

I currently have a table for:
Season
Sport (football, basketball, etc.)
Game (team and opponent and score)


any help would be greatly appreciated thank you

 

[traingamer]

You're missing at minimum a table for "Team" which contains the team id and whatever info applies to 'just that team'. The 'Game' table could have both teams as 'home' and 'visitors' if that applies, and your selection could include ... where 'visitors' equals 'teamid' OR 'home' equals 'Teamid'...

I can think of other, more complicated relationships, but as long as you're including games with just two teams, that ought to cover most of it.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[webmaniacal]

im sorry ... yes I do have a table with teams and i also made a table with opponents thinking i might need that

but even with that i then have a table "team" which lists "Cardinals" and a table with "other team" which lists the Cardinals and game which lists Cardinals v.s. Other team AND otherteam v.s. Cardinals

not very normalized is it lol i'm just not visioning this correctly i think

 

[lespaul]

ok, you have TEAMS (with PLAYERS if you want that much detail) that play GAMES.

Teams
ID
Name
Sport

Games
HomeTeam (FK to ID in Teams)
AwayTeam (FK to ID in Teams)
GameDate
HomeScore
AwayScore

then you could do a query like:

SELECT G.HomeTeam, H.Name, G.AwayTeam, A.Name
FROM Games G
INNER JOIN Teams H On G.HomeTeam = H.ID
INNER JOIN Teams A On G.AwayTeam = A.ID

would give you
Home Team Away Team
1 Cardinals 100 Other Team
100 Other Team 1 Cardinals

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[webmaniacal]

that is excellent but if i read it correctly, it would show all the games and list which team was home and away which is great for one purpose but i really need it to show by team because the visitor will most likely either view their own team or another team to see all of the games that respective team played or am I misreading it?

The visitor will probably most want to see

Select team = Cardinals

v. Bulldogs 42 -14 win


Select Otherteam 1

Bulldogs 42-14 loss

etc.

That is why I am having a problem with normalizing the data...there are 10 teams each of which play each other one time but the scores have to be reported twice and inversely

Thank you very much for looking at this

 

[traingamer]

So you're asking for the results of all of the games between the 'Cardinals' and the 'Rams' for a season?

I think Leslie and I were both thinking of giving the scores for ALL of the games for any one team.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[webmaniacal]

yes, it is ok if we want to offer as an option all of the games for the season, but i really think the visitor will be looking at team specific information ... sorry that I am not great at describing it. The important thing is the drop down box flow of

Season
Sport
Team

so that the database holds football, basketball, volleyball etc. (all for the same 20 teams) and that the visitor can view information about any sport by team...anything other than that is great as options

thank you again

 

[fneily]

Expanding on LesPaul:
First, to clarify, you mention in one post 10 teams then in another 20 teams. Which is it? It really doesn't matter, but keep your data consistent.
Using permutations, you're taking 10 teams two at a time where order matters. So 10!/(10-2)! = 90 entries. If 20 teams, you'd have 380.
So your table, as suggested, would be
ID HomeTeam VistingTeam HomeScore VisitingScore Date

From this table using comboboxes and a little VBA, you can have users select any team and get the result desired.
You'd just have two little comboboxes to select season and sport to filter the main selections.

 

[traingamer]

yes, it is ok if we want to offer as an option all of the games for the season, but i really think the visitor will be looking at team specific information

All of the games for the season for one team is team specific information.

If the user chooses season '2006', sport 'football', team 'Cardinals', what specifically do you want to display?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[webmaniacal]

it would then list all of the games that they play/will play and the score records would remain blank until played

chooses Cardinals...

game date opponent score w/l league record overall
9-1-06 Other Team 21-6 W 2-0 3-0

chooses Other Team

game date opponent score w/l league record overall
9-1-06 Cardinals 21-6 L 0-2 0-3


Thank you for looking at this!!

 

[traingamer]

Just craft a where clause that includes an OR to include both teams like:

...WHERE (([Games].[HomeTeam]='Cubs' and [Games].[AwayTeam] = 'Cards') OR (([Games].[HomeTeam]='Cards' and [Games].[AwayTeam] = 'Cubs'))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[weigoldk]

webmanical,

Is this a class assignment?