Clue Ride DB Design
Approach
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)
- On the private Wiki until this is ready for public consumption: https://1109.jettmarks.com/JSPWiki/Wiki.jsp?page=PostGresQL
Installation on Mensa (retired Jan. 2019)
- Following this link for Ubuntu: https://help.ubuntu.com/community/PostgreSQL
- Installed on mensa running Ubuntu 16.10.
- Following geospatial extras were installed:
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
- Attraction details & Location Types
- clues (in JSON, moving to puzzles in PostGreSQL puzzles page)
- Course
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.
page revision: 24, last edited: 03 Jan 2020 00:59