Clue Ride DB Design

Approach

Since much of this data is static, we're going to start out simply using JSON files to persist the information required to get a first set of courses up and running. This will also provide an opportunity to check out the DB requirements and relationships.

It won't be until we need the capabilities of ad hoc queries and reporting that we'll be interested in using a database.
Also see Storage Approach 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

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

  • The top level tree is split between a test instance and the production instance.
  • 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