SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Database Design

  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design

    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...
    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Thanks. Wouldit be better to have this one table for matches?...
    Code:
    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	
    )

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i would combine your first two flags, and pick a different name for the id

    you still have the FK problem, though
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would combine your first two flags, and pick a different name for the id

    you still have the FK problem, though
    Like this?...
    Code:
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you're on the right track

    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you're on the right track

    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
    Thanks so much!

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

    Code:
    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)
    )

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?
    Of course!

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •