Data-Base Structure

jlsports

I am having a very difficult time trying to figure out the data-base structure for a project I am working on.

I need to track information of a football game. Some of what I need is the following

GameID
GameDate
GameTime
Surface
Stadium type
Sport (Pro or College)
Game INFO
HomeTeam
AwayTeam

I also need about 25 different STATS trackerd for the HomeTeam and the AwayTeam.

Part of my problem is that I need to QUERY the data-base to get information by team and a team can end up either as a HOMETEAM or AWAYTEAM. THis means that their information might be stored as a HOME TEAM or AWAY TEAM

I setup my first data-base with all information in one HUGE data-base that contained nearly 100 colums with each row representing 1 game, but this seems wrong to me.

If somebody could could give me a nudge in the right direction that would be great. I have scoured this board and have seached the net looking for the proper way to set things up. Thanks in advance

ahundiak

You might consider using a game_team table to link games to teams

CREATE TABLE game(
INTEGER game_id PRIMARY KEY
);
CREATE TABLE team(
INTEGER team_id PRIMARY KEY
);
CREATE TABLE game_team(
INTEGER game_team_id PRIMARY KEY,
INTEGER game_id,
INTEGER team_id,
INTEGER type, -- 1 = Home, 2 = Away
..
Rest of game oriiented stats for a given team
);

jlsports

So each team would have a record in the game_team table, 2 rows for each game?

Another problem is I also need to extract data both teams because I would want to get a teams opponent and the opponents stats.

ahundiak

Basically, you end up making two queries. The first query extracts the games for a given team. The second query then extracts all the game information.

SELECT DISTINCT game.game_id
FROM game,game_team
WHERE game.game_id = game_team.game_id
AND game_team.team_id = $teamID;

Make a comma delimited list of game_ids from the results then

SELECT -whatever your need -
FROM game
LEFT JOIN game_team USING(game_id)
WHERE game.game_id IN ($gameIDs);

You end up with two records per game, one for the home team and one for the away team.

If your database supports nested queries (sadly mysql does not) then you can do this in one query.

jlsports

Thanks for the help. I will try to give this a try but I am still not totally grasping this. I currently have all the information in one huge data-base with nearly 100 colums. This works but I have a feeling that this is very ineffiecent. Any other suggestions would be greatly appreciated. Thanks

dnszero

It looks like you need to normalize your database. Basically this involves creating a structure that minimizes redundancy between rows (among other things). Before you do much else check out:

http://www.phpbuilder.com/columns/barry20000731.php3

At first glance, I'd suggest breaking it up into 3 tables: a team table, a stadium table, and a game table.

In the team table, include all the information that is specific to that team, eg, name, type, hometown, mascot, etc. Just whatever you'd want to keep track of that pertains to only one specific team. Be sure to include an autoincrement, primary key field in this table.

Do the same for the stadium table. In that table include things like, the stadium name, the location, type of stadium, seating capacity, field type, etc. Also, include an autoincrement primary key field in this table.

Now for the game table. Just like before keep track of all the stats here relating to a game, but when it comes to the teams and the stadium, just use the primary key id of the each and the key for the stadium they played in. Essentially, this is linking the tables together this way.

The main advantage to using linkagaes like this is that it makes your tables easier maintain and much clearer. Now for instance, if you realize that one of the team names is mis-spelled (or changes) you only have to change it in one place (the team table). Or if you get a new phone number for the stadium, or if the stadium adds more seating, you only have to change that info in one place (the stadium table). This is the heart of normalization and generally if you follow the 3rd Normalized form, as described in the above article, you'll wind up with DB that's easier to maintain and easier to work with. That said, 3NF (3rd normalized form) is not always the best, ie, there is no magic rule that works for every situation. But it's a good place to start.