Data Model

sparkymark75 · 25 Feb 2006 at 17:52

Can someone give me some inspiration on how to model a league in a database?

This will obviously comprise of teams, players, fixtures, results, scoring, etc. But then there's things to complicate matters such as players leaving a team during a season and playing for another team.

Lagz · 25 Feb 2006 at 20:38

Something like:

Tables:
- Player <player-id,age,name. . etc. >
- Teams <team-id,name,ground capacity . . etc.>
- Contracts <team-id,player-id> // tells you which players play for which club.
- Fixtures <fixture-id,team-id,team-id2,date,result>
- Results <fixture-id,score>
- Scorers <fixture-id,scorer>

The exact type of queries you are planning on having affect what tables you use. You could combine Contracts and Player however certain queries could take longer as a result. The same could be said for combining Results and Fixtures.

sparkymark75 · 25 Feb 2006 at 21:15

Not far off what I came up with. (Excuse the use of MS Access)

Lagz · 25 Feb 2006 at 23:11

Surely you only want 1 player table? You basically have the same table three times there?

sparkymark75 · 25 Feb 2006 at 23:20

When I try and relate the Scorer, Assist1 and Assist2 fields in tblFixtureGoals to the PlayerID in tblPlayers, it generates another 2 aliases for the tblPlayers table. There's still only one actual tblPlayers table, must be the way Access handles relationships.

Haven't used Access for years, used to SQL Server.