It's been about a month since my most recent post related to the Football Match Result Database, so I'd like to give an update on what I've been doing since then.
In March I bought a book on relational database design and completed a specifications document for the FMRD. In April I bought another book and focused on implementing the database in OpenOffice Base. The objectives were two-fold: to debug any errors in the database design, and to develop a data entry tool to load data into the database.
Implementation proved to be much more difficult than I thought. I'm fairly adept at learning languages, whether they are computer languages or human languages. But there is a very steep learning curve when it comes to database programming and database tools. I know that there are a lot of people with a lot more experience in database front-end development than me, and several of them have sent me messages offering their support and expertise, but these were skills that I needed to learn for myself. After much effort I finally solved the remaining technical problems associated with loading a junction table from the data entry form. (Forms, subforms, and queries, oh my!) That was a critical part of the design, so I can finally move forward with implementing the rest of the forms and queries.
I had to alter the design of the tblEvents table, which contains high-level information on the match and the competition in which it belongs and links to other aspects of the football match. My biggest challenge was with figuring out the best way to link the home/away teams (and home/away managers) to the events table. A team can be associated with many matches during a season, but a match can only be associated with one home team and one away team. Using two foreign keys from the same table was a big no-no to the database software, and my first attempt at a junction table didn't work either. I was able to solve the problem by deleting the foreign keys (evtHomeTeamID, evtHomeMgrID, etc.) and forming a junction table with keys from the tblEvents and tblTeams tables (or tblManagers table). It seems to be a more normalized design than the previous version.
I've rewritten the specs document to reflect these changes, and I'm calling the new version 0.9.2. I might change the name of tblEvents at some point before version 1.0; I think in terms of matches and events within matches, and my initial name of the highest-level table has turned out to be too confusing. I'm still working on the data entry development for the other tables. There will be some fields that would only be used for domestic leagues, international club competitions, and national team matches. There are also some tables that need to be designed to handle micro-events in the match (fouls not resulting in cards, corners, shots, etc.). Those additions will probably have to wait until after version 1.0.
So where do you guys fit in? I've told a few people that launching a big public endeavor is like trying to push a big boulder down a path (hopefully not uphill like Sisyphus!). It takes a lot of effort to push the rock, and the one with the idea has to do most of the heavy pushing, but once it starts rolling a lot of people come out of the background to help keep it moving. I've definitely experienced that with this effort, and I continue to be flattered and touched by all offers for assistance. As most of you know, I've formed a business to offer research and consulting services with respect to soccer analytics, and the development of databases was a means toward that end. I've had to think very hard about how to handle any offers of assistance because of the legal and economic issues involved, but I'm close to find resolution on those. I'll write more about this in the coming weeks, but in the meantime I thank you in advance for your patience.