Football Database Design (1 Viewer)

[johnjun]

Hi,

I am building a soccer database that holds results on every fixture played in all major european leagues for last 5 years. I will be importing the data into the database from txt files. I am a newcomer to access but been learning as I go.

The imported txt files have the following fields:
Div = League Division
Date = Match Date
HomeTeam = Home Team
AwayTeam = Away Team
FTHG = Full Time Home Team Goals
FTAG = Full Time Away Team Goals
FTR = Full Time Result (H=Home Win, D=Draw, A=Away Win)
HTHG = Half Time Home Team Goals
HTAG = Half Time Away Team Goals
HTR = Half Time Result (H=Home Win, D=Draw, A=Away Win)
Referee = Match Referee
HS = Home Team Shots
AS = Away Team Shots
HST = Home Team Shots on Target
AST = Away Team Shots on Target
HHW = Home Team Hit Woodwork
AHW = Away Team Hit Woodwork
HC = Home Team Corners
AC = Away Team Corners
HF = Home Team Fouls Committed
AF = Away Team Fouls Committed
HO = Home Team Offsides
AO = Away Team Offsides
HY = Home Team Yellow Cards
AY = Away Team Yellow Cards
HR = Home Team Red Cards
AR = Away Team Red Cards

I have created the following relationships

There is one division to many results
There is one season to many results
There is one referee to many results
There is one home team to many results
There is one away team to many results

I am sure this is not the best layout:
Could the season field be calculated from the date, and hence unncessary?
I have two relationships for home and away teams, would it be better to have one tblTeam with HomeTeamID and AwayTeamID in tblResults
Should I break the tblResults into clearer entities eg. create a table for bookings, goals, corners etc.

Please see link for screenshoty of my relationships
Image here

Thanks for your help in advance

 

[gemma-the-husky]

There is one division to many results
There is one season to many results
There is one referee to many results
There is one home team to many results
There is one away team to many results

a relation implies 2 tables - this means you have a division table and a season table for instance, which seem strange

can you identify the TABLES you think you need, and the FIELDS in each of them.

Post your dbs structure if you like

 

[johnjun]

I have my table structure set out like this, this is my attempt to normalise the database.

Season, divison, home, away and referee where repeating groups

The purpose of the databse will be so that I can query any of the results for any given team (will always look at home teams seperate from away teams), league, season, referee (yellow and red cards only)

tblResults
ID = Primary Key (autonumber)
FTHG = Full Time Home Team Goals
FTAG = Full Time Away Team Goals
FTR = Full Time Result (H=Home Win, D=Draw, A=Away Win)
HTHG = Half Time Home Team Goals
HTAG = Half Time Away Team Goals
HTR = Half Time Result (H=Home Win, D=Draw, A=Away Win)
Referee = Match Referee
HS = Home Team Shots
AS = Away Team Shots
HST = Home Team Shots on Target
AST = Away Team Shots on Target
HHW = Home Team Hit Woodwork
AHW = Away Team Hit Woodwork
HC = Home Team Corners
AC = Away Team Corners
HF = Home Team Fouls Committed
AF = Away Team Fouls Committed
HO = Home Team Offsides
AO = Away Team Offsides
HY = Home Team Yellow Cards
AY = Away Team Yellow Cards
HR = Home Team Red Cards
AR = Away Team Red Cards
Lookup to tblDivison
Lookup to tblSeason
Lookup to tblHome
Lookup to tblAway
Lookup to tblReferee

tblSeason
Season

tblDivision
Division

tblHome
HomeTeam

tblAway
AwayTeam

tblReferee
Referee