Help with database design, please.

eXor · 6 Sep 2008 at 16:09

I've got a mountain of EPL data that is too unwieldy for a spreadsheet, so I thought I would try and organize it into a database.

I'm a complete newbie at this database stuff ( couple of hours ), so don't crucify me.

I have team names stored twice which is a bit redundant. Suggestions for improvement?

dan0r2 · 6 Sep 2008 at 21:28

Good start.

You should have a 'team' table. Then the 'home_team' and 'away_team', would just contain a team_id.

Another point is to use singular names for tables rather than plural - helps when doing queries e.g. team_id makes more sense than teams_id.

For your matches table prefix all fields with match_.

philjohn · 6 Sep 2008 at 21:51

Id also dispense with having "team_name" as the name field, defeats the purpose of having a table name if you ask me, you can always use an alias (SELECT teams.name AS away_team_name).

Haircut · 7 Sep 2008 at 09:09

dan0r2 said:
Another point is to use singular names for tables rather than plural - helps when doing queries e.g. team_id makes more sense than teams_id.

For your matches table prefix all fields with match_.

I would actually disagree with those two points, but naming conventions are very much personal preference.
The thing to make sure of is to choose a naming convention and ensure you stick to it.

One other point with regard to the database design - it might be worth thinking about how extensible the design is. One thing I can think of is if you want to know what minute a goal was scored it would require significant changes to your database structure at the moment.

It could be that you never need to record that, but it's worth while being aware of these things.

dan0r2 · 7 Sep 2008 at 12:36

Haircut said:
One thing I can think of is if you want to know what minute a goal was scored it would require significant changes to your database structure at the moment.

Table 'goal' with fields - goal_id, match_id, team_id, goal_time.

Isn't too bad.

eXor · 7 Sep 2008 at 13:41

Thanks for the feedback.

Haircut said:
One other point with regard to the database design - it might be worth thinking about how extensible the design is. One thing I can think of is if you want to know what minute a goal was scored it would require significant changes to your database structure at the moment.

It's like you read my mind as I've been planning to add that. Time of goal scored, scored by player x. I thought of sticking it onto the end of the match table but there would be too many repeating columns ( goal 1, goal 2 ... goal 10 )

I also plan to store 1-minute price data for different markets e.g. match odds, under 2.5 goals e.t.c.

13:30:01,3214762,'Chelsea',1.39,17383.0,1.4,35287.0,1.41,43281.0,1.42,17582.0,1.43,13021.0,1.44,2870.0,'Tottenham',10.0,4588.0,10.5,639.0,11.0,479.0,11.5,860.0,12.0,2533.0,12.5,1204.0,58805,'The Draw',4.7,1102.0,4.8,176.0,4.9,2308.0,5.0,518.0,5.1,1610.0,5.2,997.0

Like I said, unwieldy.

Haircut · 7 Sep 2008 at 15:16

dan0r2 said:
Table 'goal' with fields - goal_id, match_id, team_id, goal_time.

Isn't too bad.

Yeah, it's not too much to add if you plan for it.
My point was really that if you create the database and then populate the data using the schema shown above it would be quite a bit of work to get that data into the new structure.

To the OP, as dan0r2 says, you would want a separate table for goals.
If you're going to include the player that scored the goal you may want to have a players table as well.
What do all of the fields mean in the sample you have posted?

eXor · 7 Sep 2008 at 16:06

Haircut said:
What do all of the fields mean in the sample you have posted?

13:30:01,3214762,'Chelsea',1.39,17383.0,1.4,35287. 0,1.41,43281.0,1.42,17582.0,1.43,13021.0,1.44,2870 .0,'Tottenham',10.0,4588.0,10.5,639.0,11.0,479.0,1 1.5,860.0,12.0,2533.0,12.5,1204.0,58805,'The Draw',4.7,1102.0,4.8,176.0,4.9,2308.0,5.0,518.0,5. 1,1610.0,5.2,997.0

time, money_matched_so_far,

home_team,
3rd_best_back_odds, money_available_at_3rd_best_back_odds,
2nd_best_back_odds, money_available_at_2nd_best_back_odds,
best_back_odds, money_available_at_best_back_odds,
best_lay_odds, money_available_at_best_lay_odds,
2nd_best_lay_odds, money_available_at_2nd_best_lay_odds,
3rd_best_lay_odds, money_available_at_3rd_best_lay_odds,

away_team,
3rd_.....

draw,
3rd_.....

There's a record like that per minute, per market, per match. 120 minutes * 5 markets * 1 match.

'match odds' is classed as one market but there are 3 sets of odds data within that market.