Hi,

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)
Competitions
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.

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

Hi,

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.

Moved to Database Concepts & Design - dBforums

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?

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
eg
competition
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?

Hi

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....

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