Over the last three days I've made significant progress on the implementation of my database design. I have used OpenOffice Base 3.0 to build the database and the entry forms, and after much effort I was able to design forms that could load all of the major tables of the database. It was a very rough form design which would have gotten unwieldy when dealing with multiple matches in separate rounds, so I incorporated a filter with some of the entry tables to make the process a little less error-prone. I've now progressed to the point where I can fully test all of the tables in my database and verify the integrity of the data sent to them.
The current database design handles results from a league with no playoff stage, and I used data from the 2009 CONCACAF Hexagonal to load and test the database. (There are no tables to handle micro-events that occur during a match, such as fouls, corners, shots, etc., and I will explain why in a future post.) The database design looks very good and reliable and I am close to designating the current specifications as version 1.0.0. I need to include some documentation of the views that I developed before I update the version number. I'll try to complete that in a day or two.
Now the whole process, from database design to a working implementation of the data entry forms, has taken almost three months, and I could have significantly reduced that time had I hired somebody or even accepted one of the major offers of assistance that I received. In the end, I felt this was knowledge and expertise that I needed to acquire for myself, and I feel that I have a greater understanding and appreciation of database design and implementation for having gone through the full process.
Obviously there are other types of competition formats in soccer, as well as friendlies and exhibition matches that don't cover any format at all. So there will be some extensions to the database to add functionality. My expectation is that some tables will be used for certain formats and not at all for others. I'll have a post later today where I identify future extensions to the current database design.
The bottom line is that I am quickly approaching the point where I have a reliable storage mechanism for match result data in soccer. The next step in the soccer analytics process is to write software that can interact with the database to extract the relevant data that I need.