Football Match Result Database: Future extensions

As I mentioned in my previous post, the current database design captures matches that occur under a league format without playoffs, at the club and national team level.  (Implementation for national teams is a little simpler and redundant at the same time; the tblTeams table is superfluous and can be replaced by tblCountries when designating competing teams, and the integrity of the player entry can be strengthened because one knows that players won't switch national teams.)

Extensions to the database would have to allow for modeling of following competition formats:

  • Knockout tournaments (single or multiple legs in each round)
    • Cup competitions
    • League playoffs
  • Hybrid tournaments that are comprised of a league and knockout phase
    • Continental club competitions
    • Some domestic club competitions
    • World Cup/Olympic tournaments
  • Friendly/exhibition matches (club or national team)

These extensions would require additional functionality in the database design, and perhaps additional tables.  The resulting design needs to maintain the relational structure of the overall database.

Below are some of the extensions that are on my to-do list:

  • Include geographical data for match venues in tblVenues (decimal degrees)
    • Latitude
    • Longitude
    • Altitude (move Altitude field to tblVenues for consistency reasons)
  • Designate captain in match lineup
  • Consider default position for players (*)
  • Extensions for knockout competitions:
    • Round
    • Matchday
    • tblPenaltyShootouts table
  • Extensions for hybrid competitions:
    • Phase (League/Knockout)
    • Group
    • Round
    • Matchday
    • tblPenaltyShootouts table

I starred the 'default position' extension because I felt that it warranted further discussion.  I did not include a position field in my players table because I wanted to account for players who are inserted at various positions in different matches.  It's true that most players will stay at a fixed position over their careers, which makes data entry annoying, but I haven't thought of a good way to implement the notion of a default position.

And that gets to the issue of implementation.  I am currently using Base v3.0 to implement the FMRD, and it has definitely been a learning experience, with shallow learning curve included.  It's taken a long time to get the data entry forms working correctly, but they do work.  At some point I will put together a repository for the implementation and documentation, but I have a lot going on right now.  Now that I have a working implementation, I'm looking at other options such as using postgreSQL and perhaps Treshna's Bond toolset to set up data entry forms.  With any luck, implementing the database in postgres won't take as long as it did to do so in OOo, but I'm rarely that fortunate.

So that's where I am with the database design.  Now I need to put together a nice set of tools that extract data from it in the format that I desire.