Help with database design, please.

eXor

eXor

eXor

Soldato
Joined
13 Nov 2002
Posts
3,589


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.:p

I have team names stored twice which is a bit redundant. Suggestions for improvement?
 
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_.
 
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).
 
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_.
Click to expand...

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.
 
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.
Click to expand...

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
Click to expand...


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

Isn't too bad.
Click to expand...

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?
 
Haircut said:
What do all of the fields mean in the sample you have posted?
Click to expand...

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
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top Bottom