The following data model is designed to hold information relating to Teams, Players and Fixtures for a Football or Soccer Team. For this scenario we need to define the following facts:
These facts define the requirements which the Database must meet and should be agreed between the Database User and the Database Designer prior to physical creation.
The draft facts have been defined as:
The Entities required should include:
- Player Positions
The Entities are related as follows:
- One Team can have Many Players
- One Player can play in Many Fixtures
- One Fixture has Many Players
With this design, One Team has many Players, however a Player can only play for One Team at any one time. The relationship between Players and Fixtures in a Many-To-Many relationship. One Player can play in zero, one or Many Fixtures, and the Fixture involves Many Players. The link table between this relationship also allows you to record whether a Player scored any Goals in that particular Fixture.
When asking questions of the database we may need to know:
- Which Players played in a particular Fixture?
- What was the result of a particular Fixture?
- Which Players does a particular Team have signed on?
The following data model allows these questions to be answered and allows the information contained above to be stored logically and in a structured manner.
To view the model please go to: Relationships for Football or Soccer Team Players and Fixtures
Please download the data model relating to Relationships for Football or Soccer Team Players and Fixtures which is contained in the zipped up Microsoft Word document.