help setting up table structure

sks1024

I have a client that manages a local parks and rec basketball league. They want to be able to show the scores of the weeks games on their website. I am strugling trying to figure out how to set up the tables in the db to accomplish this and how to join them using mySQL.

He is my first crack at it. Each table linking to the other by the id.

Please help.

Thanks

tblsports:
sport_id
sport_name

tblteams:
team_id
sport_id
team_name

tblresults:
result_id
team_id
result
result_score1
result_score2

ahundiak

You will probably want a games table:
tblgames
game_id
date_played
location

Now comes the question of linking teams to games. It's tempting to just add some columns to the games table:
home_team_id
away_team_id
home_team_score
away_team_score

But duplicating columns can be troublesome. If you want to query for games for a particular team then you need to start saying things like
WHERE home_team_id = team_id OR away_team_id = team_id
Gets to be a pain.

I'd suggest making a relation table
game_team
game_team_id
game_id
team_id
game_team_type ; 1 = Home, 2 = Away
score
With two entries for each game.
Now the queries become easier.

You might want to take a look at this open source soccer management program:
http://sourceforge.net/projects/zayso
It's still very much in development but has been used for several seasons. Browsing the table structure might give you some ideas.

sks1024

What do you mean when you say relation table.

Stan

ahundiak

A relation table is just an ordinary tables that exists to relate records between two (or more) other tables.

So, for example, you have a games table and a teams table. To specify which teams play which games requires some sort of relationship between the two. Hence the game_team table.

If this is a new concepts for you then you might want to find some relational database tutorials.