Firstly, sorry for the long post, but if you have got the time to read it and help Iíd appreciate it!
Iíve been tasked of creating a database for my local football club and needed a few bits of advice.
Iím imaging that after each game I could load up a form, and enter; the date of the game, the opponent, if it was home or away, then put in the starting 11, who came on as a sub and who scored or got booked etc. This would then somehow (this is the bit I donít know how! And makes me wonder if some kind of spread sheet would be better) update a table or two with the stats totals. This would hopefully then not only update their season totals but club career totals as well.
I realise there is websites (such as clubwebsite.co.uk) for the above bit, but we want our own database so that we can search through the data and pull out want we specifically through queries. The idea is that through creating the database we will be able to keep records up to date of most appearances, most goals, most yellow and red cards etc, as well as being able to draw on stats before each game such as the last five results between the two teams, who has scored the most against them, if anyone is about to reach a milestone such as 50 goals or what not (a lot like what the game Football Manager 2010 did before a game).
Itís a new team thatís only been going one season so shouldnít take too long to input last years data. What I envisage that I need to do is set up the following tables;
1) Player Table; All players details such as name & joining date
2) Teams Table; A table with all our opponents team names
3) Venue Table; a table with home, away & neutral in
4) A New player form; so that I can add players into the player table
5) A New Team form; so that I can add teams into the team table
6) A Game form; So that I can enter the details of the game as mentioned in one of the earlier paragraphs.
I realise that I need to give certain things a unique ID such as the player, teams and venue.
So my questions are;
a) would I need some kind of stats table? What would go in here do you think?
b) Also how does the database give me totals? Is that possible?
c) Is a database the best way to go?
At the moment a part from setting up those basic tables Iím kind of at a loss of how to progress with it. I donít know where the data that I enter in my game form would go? Im a fairly basic user when it comes to databases and the like but I can actually turn on a computer which puts me ahead of most of the lot in my team (hence why I got volunteered to do this!)
I have searched for maybe a template on the web but I cannot seem to find one which is a shame.
I have a Data Model for Football (both soccer and American Football) on this page of my Database Answers Web Site :- Football Clubs Data Model
The answer to your question about totals is that the rules for Relational Theory and Normalisation mean that derived values (which includes totals, of course) should not appear in Conceptual Data Models (like mine).
Producing a few totals can easily be done in SQL but the professional or sophisticated answer is to create a Data Warehouse which solves this problem by storing totals in a table with a particular design based on what are called Dimensions and Facts.
I don't have one for the Football Clubs but maybe I can find time to cradte one for you. if you need it, email me at firstname.lastname@example.org.