Sorry for being away from the site (actually both sites) for a while, but I was preoccupied with completing this database specification task. I've finally arrived at a point where I can share my plans with someone else, which I will do in a few moments.
First of all, I would like to acknowledge those who came forward and offered to assist me in the creation of this match result database. It's not that I didn't consider your expertise to be valuable; I did and still do. However, I needed to build my own level of expertise with database design and I also needed to take ownership of whatever design was developed. Your help would still be very useful on another part of the development process, which I will discuss in a future post.
I also want to recommend Database Design for Mere Mortals by Michael Hernandez. I used this book to guide me through the database design process and it proved extremely valuable in building my own level of expertise and creating — what I hope! — is a robust and reliable database design.
Okay, so without any other delay, here is the specification document for the Football Match Result Database. It's a comprehensive document — 55 pages long. I call it version 0.9 because even though the design is quite advanced, I have not done a comprehensive debugging of it. I need to create a linking table between Teams and Venues (multiple teams can share a stadium, and a team can play at multiple stadiums over many seasons). I also need to include views. I must also point out that this database is currently set up for league competitions. It can be extended to knockout and hybrid competitions (the former is easy, the latter a little less so), but I haven't written specifications yet. Also, the database does not include in-match statistics such as shots, assists, corners, fouls that don't result in bookings, etc. The reason is that I wanted to focus on historical results and in-match statistics were rarely tabulated until very recently. These statistics can be included in a new table for the database.
Great, that turned out to be another delay. Here's the document. I have to go to work now, I'll have more to say about it later.