Database Design
 

Hey folks, I need some help! I'm working on a project and I need to design the database. The thing is I could use some help as the database could possibly be quite complex, and I've never done anything of this size before.

The project is for a football team, and will work similar to that of web-teams (example here: http://www.webteams.co.uk/Home.aspx?...laswanderersfc). The site will have all the same features to start, such as fixtures/results, league table, stats etc etc. I want the site to also accept user registration as I will through time be integrating a message board and other features.

Could anybody help me? I'm not sure what information you'll need in order to help me so please tell me what you need to know :-).

Thanks,

To start you off, all the football teams should be centralised in one table. So Everton, naturally being first, Chelsea, Arsenal, all listed in one table. And then when you add other tables, you will simply set the appropriate team_id corresponding to the team table.

Yeah, that sounds good. How would I handle each result? Because each match day could have 10 different results from the league, or it might have 8 or whatever, and there might be 20 match days a season.

There might also be another 10 cup games which would not count towards the league table etc.

How would I calculate the league table using results in the db?

Well, you could use a fixture table as the next main thing to work on...

fixture {date, location, home, away, score_home, score_away}
if that is a bit too cumbersome with the natural triple composite key, add fix_id as well.