Table design question

[neil40]

I have a question on the best way to design a database I want to
create.

My database will be keeping records of historic League tables for a
certain sport.
For a given team, who may have been in several different leagues in
their personal history (for example Southern League, Elite League etc)

Ultimately, I want to produce reports that show 1 or all of these
Leagues in the way you might see them printed in a sports paper/
magazine (Wins/Draws/Points etc), and also a report listing any given
clubs history (it would have a year by year record of the teams).

So, I would like some advice please on how best to store the data in
tables.
I.E. is it best to create a record for 'ABC United' for each year they
competed or do it in another way?
My initial thought was a record like this:
Team/Year/League/Won/Drawn/Lost/For/Against/Points
ABC United/1950/Southern League/22/2/10/1356/1058/46

The League name could come from a linked table with a list of the
valid League names?

Many thanks for any advice.

Neil

 

[Guest]

Hi Neil,

You generally only want to store information in a single place in a
database, and reference it from there as needed. You will need several
tables:

tblTeam
TeamID autonum
TeamName text
etc

tblTeamResult
TeamResultID autonum
TeamID num (links to TeamID in tblTeam)
TeamResultYear num
TeamResultLeagueID num (maps to LeagueID in tlkLeague)
TeamResultFor
TeamResultAgainst
etc

tlkLeague
LeagueID autonum
LeagueName text
LeagueDescription text
etc

Hope this gets you started.

Damian.

 

[neil40]

Thank you Damian.

I'm a little confused by your statement though:-
....store information in a single place... You will need several tables

Isn't that a contradiction?

Neil