Clue Ride DB Design


Since much of this data is static, development started out simply using JSON files to persist the information required to get a first set of courses up and running. This approach provides an opportunity to check out the DB requirements and relationships.

Ad hoc queries, volume of data to manage and reporting push us toward using a database.
Also see Storage Approach - Network with diagram.

Relational Database

  • Use of PostGreSQL with GeoSpatial extensions
  • Schemas along permission levels:
    • Public Infrastructure: Segments and Ratings database
    • Sponsored POI (belonging to local businesses and organizations)
    • Users accounts

Installation on Dorado (Jan 2019)

Installation on Mensa (retired Jan. 2019)

postgis - Geographic objects support for PostgreSQL
postgis-doc - Geographic objects support for PostgreSQL -- documentation
postgis-gui - Geographic objects support for PostgreSQL -- GUI programs

Start with

Suitable for JSON

Proposed Structure on Disk

This is out-of-date as of Jan 2 2020: Only images are stored out to disk. Everything else is in the database.

  • The next level down is called 'locations', but it also holds nodes and node groups as geojson files.
  • Under the locations directory, there is a subdirectory for each location with the format "loc-000ID" — the character sequence 'loc-' followed by a 5-digit ID with leading zeroes. (100,000) locations per city should be fine for now, but I may run out of inodes.
  • Under each of those directories, there would be
    • exactly one loc-000ID.json file with the main details of the location.
    • at least one clue-0000ID.json file (1 million IDs), with as many as we can come up with for the location.
    • zero or one estab-000ID.json file (which also contains the hours data) (0.2 release) — If there are more than one establishments, they become a different location and possibly part of a LocationGroup.
    • images.json for holding the Image URLs
    • tags.json for holding the array of tags

The directory jsonFeatures/locatoins/loc-00001 is an example.

More suited to DB

  • Badges - and their relationship to User Profile
  • User profile - Tracking from one session to the next.
  • Teams - if we wanted a history of participation.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License