Is this the best table setup

ericg212

I am new at this so excuse any obvious ignorance.

I am setting up a sport league system using MySQL, basically their will be several sports, each sport having several leagues within them, each league having several teams within them. I also need to post each teams schedule and results of the games - win, lose, tie and the score.

I have set up 4 tables as follows:

Table One: id, sport, description
Table Two: id, league, description, sportid
Table Three: id, team, schedule, leagueid
Table Four: id, result, score1, score2, teamid

basically the "id" field for each table will be an auto incremented number and the tables will relate to one another by the sportid, leagueid and teamid fields carrying over fromt the previous tables id.
SportOne id=1
SportTwo id=2
LeagueOne id=1, sportid=1
LeagueTwo id=2, sportid=1

So SportOne has two leagues assigned.

My question is this: is this the best way to set up the tables, are their to many or not enough.

the function will be:
For an admin to have access to add, update, delete sports, leagues, teams, schedules, scores and results.

For a browser to view the sports, leagues within the sports, teams within the leagues their schedules, results and scores.

matto

hi,

sounds well modeled to me. I'd do it the same way.

egasker

Are the column names in your tables the 'actual' names? The only thing I'd mention is that you might want to clean up the column names a bit by being more specific. If you already know this, then maybe this can help other newcomers. Perhaps something like this would better describe your environment?

Table One:
sport_id
sport_name
sport_description

Table Two:
league_id
sport_id
league_name
league_description

Table Three:
team_id
league_id
team_name
team_schedule

Table Four:
result_id
team_id
result
result_score1
result_score2

MySql expects 'you' to handle the referential integrity, meaning, you need to make sure your foreign keys match each other in order to do successful table JOINs in your SQL query. Other databases will likely spew out errors if your keys aren't matched (that's the only reason why I mention tidying up the field names). Also, if anyone else ever works with the tables, it would be helpful to have a more intuitive naming convention.

Hope this helps?

ericg212

Yes they are the actual names other than the tables are actually named as follows:
Table One - Sports
Table Two - Leagues
Table Three - Teams
Table Four - Results