Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009

    Building A Football Soccer Backend Database


    I am looking to create a database for my football(soccer) team as a backend CMS website. But I am having trouble with figuring out some of the relationships ?
    Some of the tables I have thought of include(I have all the information I want for each table) just don't know how to figure out the relationships?:

    Players(Personal Information)
    Player Stats(Squad number,Position,Favoured Foot,etc etc)
    Teams(Name,Location,Contact Details)
    Fixtures(location,date,kickoff time,type)
    Results(score,yellowcards,redcards, free kicks, corners,etc etc)
    League Tables(team name, played,won,drawn,lost,for,against,gd,pts)
    etc etc etc.

    The best way I can think off describing how I would like it to work is to think of Championship Manager/Football Manager games ?

    Any help would be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    do you know about primary and foreign keys?

    if not, have a nose around bing or google, then come back and see if you can define the primary and foreign keys for your tables

    if you run into trouble, we'll still be here | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009

    Football Database


    Yes I understand about primary and foreign keys and but i get stuck on building the relationships part, whether they should be 1 to 1 or 1 to many or many to many ?

    I got some of the following :

    1 team can have many players,
    1 position can have many players
    1 player can play in many different fixtures
    Fixtures can have many players
    competitions can have many fixtures

    so those are some of the basics, but I still want some for individual matches and statistics, league tables.
    etc etc.
    My keys are just basic names like, PlayerID, TeamID, FixtureID, CompetitionID.

  4. #4
    Join Date
    Jan 2007

  5. #5
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by MrFootball50
    1 position can have many players
    if you are interested in the positions in and of themselves, then you need a positions table

    if you want to ensure that you never assign a player to a position that doesn't exist, then you need a positions table

    otherwise, you don't need a positions table, and you can carry the player's position as a non-key attrribute in the players table

    here's a parallel that might focus your thoughts --

    it's exactly the same scenario with first names

    if you are interested in the first names in and of themselves, then you need a first names table

    if you want to ensure that you never give a player a first name that doesn't exist, then you need a first names table

    otherwise, you don't need a first names table

    does that help? | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    out on a limb
    well here's a rough model for a football league
    I don't claim its finished
    I don't claim its 'right'
    there's some things that are a bit flaky
    not all columns are defined
    some things are a bit ugly (eg the way the player is mapped to a vclub and used in a fixture...
    there's no reason why the Player ID couldn't be used in place of playerid & club ID

    the position is modelled in a differnet way
    I'd evisgaed that a player playes in a specific position and saw it as a row /position
    row 0 not on field, 1 = keeper, 2......6
    posion 1 = far right, 2 = midriight, 3 = centre and so on
    that should be flexible enough to handle whatever formation a team wants to play eg 442, 451, 4,1,2,1,2 or whatever

    however its not finished
    its not signed off
    it may not even be appropriate.
    in the event table there's space for player a and an optional player B
    player A must always exist, player b not required.
    so it should be possible to identify say Player A fouled Player B, or Player B tricked the referee into giving a booking to player A Player A scored a goal, Player A went on a mazy run....

    I know there's more work to do, I know this model skews down a specific pathway but its an example of what a model could look like

    it makes a basic assumption that all fixtures are within some form of annulised competition eg EPL, FA Cup, League Cup, Championship.. whatever
    each such competition is subdivided into years
    if there are replays or two legs to a specific level then they have to be defined as another level
    FA Cup: Round 1,2,3,4,5,QuarterFinal,SemiFinal, Final
    if there were say two legs in round 5
    then you would need to define
    FA Cup: Round 1,2,3,4,5.1,5.2,5.Replay1,QuarterFinal,SemiFinal, Final

    the referee entity may need revisiting if say you want to track who refereed what in different leagues
    ferisntance a specific referee may be qualified to referee in specific competitions, eg may be allowed to referee UK matches but not internationals and so on.

    ultimately a data model is you representation of the business requirement, and as such any data model you come up with has to support your assumptions of what the business requirement.

    some peopel may add tables which aren't required.. eg you coudl argue that fixture comments isnt required.. depends on what you want. it may be that a fixture gets rescheduled, or called off, or cancelled halfway through.

    its not unknown for matches to get abandoned and repalyed but how do you model that team A were 5-0 up when the lights failed and in th erematch they lost 2-4

    one thing the fixture table doesn't do is store the actual location of where the game was played.. thats something you may need to consider

    also I haven't stored the final score based on the ability to derive that from the Fixture Events.. you will need to think about how a fixture event is coded. as I read it goals are either scored by a player on the attacking team or scored by a player on the defending team (an own goal).
    by dumping the events into a separate table its possible to say record the number dives, penalties or what ever other tricks used by scumbags such as ManUre to connive their way to being the worlds greatest football team TM*

    *what me bitter?
    Attached Thumbnails Attached Thumbnails footballmodel.png  
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2011

    ER for Football database


    Do you have any ER or SQL Model for Football Database?

    I tried to build database from that picture attached but it is so difficult because it is missing to many things....

  8. #8
    Join Date
    Apr 2010
    London, England

    Here's a Data Model for Soccer (and NFL) Clubs

    Here's a Data Model on my Database Answers Web Site for Soccer (and NFL) Clubs that might help you with some insights :-
    Football Clubs Data Model

    Barry Williams

Posting Permissions

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