Database Design

Databases
1

Hi,
I would be delighted if you could offer suggestions on my database design below.
I want to create database tables to store football match results, fixtures, and standings.
Based off the FIFA website.

Here is my DB design…


CREATE TABLE Groups
(
	GroupID char(1) PRIMARY KEY,
	GroupName nvarchar(10)
)

CREATE TABLE Teams
(
	TeamID int,
	TeamName nvarchar(60),
	TeamCode char(3),
	GroupID char(1) references Groups(GroupID)
	PRIMARY KEY(TeamID, TeamCode)
)

CREATE TABLE WCGroupMatches
(
	GroupMatchID int PRIMARY KEY,
	MatchDate datetime,
	HomeTeam char(3) references Teams(TeamCode),
	AwayTeam char(3) references Teams(TeamCode),
	HomeScore int,
	AwayScore int
)


CREATE TABLE WCKnockoutMatches
(
	KnockoutMatchID int PRIMARY KEY,
	MatchDate datetime,
	HomeTeam char(3) references Teams(TeamCode),
	AwayTeam char(3) references Teams(TeamCode),
	HomeScore int,
	AwayScore int,
	ExtraTime char(1),  -- 'Y'/'N' flag
	PenaltyShootout char(1)  -- 'Y'/'N' flag
)

CREATE TABLE Goalscorers
(
	GoalscorerID int PRIMARY KEY,
	MatchID int references WCGroupMatches(GroupMatchID),
	TeamName char(3) references Teams(TeamCode),
	GoalscorerName nvarchar(100)
)

Thanks!

1 Like
2

i think it is time for you to actually test your SQL, because learning to handle error messages and take corrective action is a much better skill to have than asking for people to comment on your last design revision

you still have an error in the goal scorers table

also, what happens if someone scores two goals in a match?

3

Thanks so much!

Here is my updated database…added a couple of columns to WCMatches table…


CREATE TABLE Groups
(
	GroupID char(1) PRIMARY KEY,
	GroupName nvarchar(10)
)

CREATE TABLE Teams
(
	TeamName nvarchar(60),
	TeamCode char(3) PRIMARY KEY,
	GroupID char(1) references Groups(GroupID)
)

CREATE TABLE WCMatches
(
	MatchID int PRIMARY KEY,
	IsKnockoutMatch char(1),  -- 'Y'/'N' flag
	MatchDate date,
	HomeTeam char(3) references Teams(TeamCode),
	AwayTeam char(3) references Teams(TeamCode),
	HomeScore tinyint,
	AwayScore tinyint,
	ExtraTime char(1),  -- 'Y'/'N' flag
	ExtraTimeHomeScore tinyint,
	ExtraTimeAwayScore tinyint,
	PenaltyShootout char(1),  -- 'Y'/'N' flag	
	PenaltyHomeScore tinyint,
	PenaltyAwayScore tinyint
)

CREATE TABLE Goalscorers
(
	GoalscorerID int PRIMARY KEY,
	MatchID int references WCGroupMatches(GroupMatchID),
	TeamName char(3) references Teams(TeamCode),
	GoalscorerName nvarchar(100)
)
4

Thanks. Wouldit be better to have this one table for matches?..


CREATE TABLE WCMatches
(
	GroupMatchID int PRIMARY KEY,
	IsGroupMatch char(1),  -- 'Y'/'N' flag
	IsKnockoutMatch char(1),  -- 'Y'/'N' flag
	MatchDate datetime,
	HomeTeam char(3) references Teams(TeamCode),
	AwayTeam char(3) references Teams(TeamCode),
	HomeScore int,
	AwayScore int,
	ExtraTime char(1),  -- 'Y'/'N' flag
	PenaltyShootout char(1)  -- 'Y'/'N' flag	
)
5

Like this?..


CREATE TABLE Teams
(
	TeamName nvarchar(60),
	TeamCode char(3) PRIMARY KEY,
	GroupID char(1) references Groups(GroupID)
)

CREATE TABLE WCMatches
(
	MatchID int PRIMARY KEY,
	IsKnockoutMatch char(1),  -- 'Y'/'N' flag
	MatchDate datetime,
	HomeTeam char(3) references Teams(TeamCode),
	AwayTeam char(3) references Teams(TeamCode),
	HomeScore int,
	AwayScore int,
	ExtraTime char(1),  -- 'Y'/'N' flag
	PenaltyShootout char(1)  -- 'Y'/'N' flag	
)
6

i would combine your first two flags, and pick a different name for the id

you still have the FK problem, though

7

Of course! :blush:


CREATE TABLE Goalscorers
(
	GoalscorerID int PRIMARY KEY,
	MatchID int references WCMatches(GroupMatchID),
	TeamName char(3) references Teams(TeamCode),
	GoalscorerName nvarchar(100),
	NumberOfGoals tinyint
)
8

you’re going to run into some trouble with the two-column PK in teams

for instance, the WCGroupMatches table has two references to the teams table, and those won’t work because they don’t reference the entire PK

pick either the id or the code as the PK for the teams table

also, why aren’t you interested in the goal scorers in knockout matches? i think your two matches tables should be combined

9

you’re on the right track :slight_smile:

i would use DATE instead of DATETIME for the match date

and i would use TINYINT (or SMALLINT if TINYINT isn’t available) for the scores