Board Game Play Tracking Using SQLite Part 1: Overview

I currently track my board game plays using Google Docs and a Python script that I wrote.  When I first started learning MySQL, I wanted to create a database for this data.  However, I saw pretty quickly that it would be too in depth for a first project.  After designing my firearms inventory database, though, I think this one should be pretty easy.

Much like my Google Docs tracking, I want to track:

  • Game
    • Game name
    • Total hours playing
    • Average playtime
    • Average score
    • Lowest winning
    • Highest winning
    • Lowest losing
    • Highest losing
    • Times Troy beat me
    • Times I beat Troy
    • Troy’s total points
    • Troy’s average points
    • My total points
    • My average points
    • Average number of players
    • Number of locations
    • Each player’s scores
  • Players
    • Player names
  • Location
    • Location names
  • Plays
    • Game Duration
    • Date Played
    • Winner
    • Location
    • Who wins between my boyfriend and I

 

As with all of these projects, this list is living and may very well change before this is over.  Some of it, particularly the ‘Game’ things I want to track, may just be calculated and won’t need to be in the actual database.  My first schema will look like this:

 

boardgameplays

 

Notice this project will have a many-to-many relationship (each player can have many plays and each play can have many players).  I believe this is the first many-to-many relationship that I’ll be showing here, except for when I introduced the idea in my post about relationship types.

In the next part, we’ll start trying to build this database.  I plan on using this project to learn SQLite, so it should be good!

2 thoughts on “Board Game Play Tracking Using SQLite Part 1: Overview

  1. Interestingly my local gaming group *just* started tracking plays as well, so this is going to be very helpful for us. I haven’t been able to find an out-of-the-box database or app or anything else to use that will fit my needs, but this will help me build my own. Thank you for taking the time to write out these step by step instructions!

    1. Do you have a BoardGameGeek account? You can track all kinds of play data through their website and that may very well be enough for your group.

      If you have an iOS device, I highly recommend the Board Game Stats app. It can automatically push your play data to BGG, which is great, but it also generates some cool in app statistics as well. If you don’t have an iOS device or if you aren’t interested in paying $3 for an app, there is a web app called ScoreGeek that can be used on PC or mobile and it is alright. I’ve heard decent things about the Android app ScorePal as well.

      I also wrote a script to pull my board game plays into a Google Sheets spreadsheet. I posted a little bit about that here, if you are interested.

Leave a Reply

Your email address will not be published. Required fields are marked *