DB Design for a Course


These may be expanded as Use Cases:

  • As a Player playing the Game, I want to retrieve the map geometries for:
    • Location -> Puzzle
    • Path (ordered list of LineString Edges)
    • Start / Stop Nodes

so I can get map support for playing the game.

  • As a Guide assembling a Course, I want to piece together an ordered list of Edges that connect two Locations, so I can can create Paths within a Course.
  • As a Network Editor maintaining the Network, I want to see what Edges and options for connecting them are available when adding a Location, so I can provide a complete set of safe and pleasant edges to construct the Path connecting that Location to the Network.

Three Types of Course Data

There are three basic types of data determined by the selection of a Course:

  • Map Features — Static except for Multi-Path Courses. Always placed within a Layer on the Map. Always GeoJSON FeatureCollection (or perhaps a single Feature at a time)
  • Game State — Pushed from server and synced across Team Members. Keeps everyone in step with Sleuthing vs. Rolling along the Paths set forth by the Course.
  • Presentation Data — Puzzles, Location descriptions, images, links requested asynchronously via REST calls under primarily Player control.

Game State Representations

From the players perspective, the game proceeds as iterations of the following:

  1. Location
  2. Puzzle tied to that Location
  3. Upon solving the Puzzle, a Path to the next Location is revealed. Get on your bikes and ride.

There is usually a Location at the end of the Path that is revealed, but that Location and its Puzzle are not revealed until everyone has arrived (the Guide gives a signal to reveal the Puzzle).

A Course is the sequence of Location, Puzzle and Paths to the next iteration. Since the Path references the Location, and the Location references the Puzzle, the Course can be thought of as an ordered series of Paths.

Persisted Representation

Progression from a practical implementation point of view:

  1. Support Outing View
  2. Support static Course (single path from start to destination)
  3. Support Multi-Path Courses

Supporting OutingView

This is the set of data that is required for a first cut at the Course:

  • ID
  • Name
  • Description - one liner
  • URL - Link back to the Course Page
  • Start Location - Provides the LatLon for a "Start Pin"

Course Type ID is probably covered by the Description.

Supporting Static Courses


Multi-Path Courses

As a first cut, Courses are stored on disk as JSON files. A few criteria:

  • Human readable names for Locations. Index can be checked upon reading the Names using the Course Editing Tool.
  • Paths between Locations can be recorded as an ID. Note that when a Course is constructed, to avoid sensitivity to changes in the underlying network, the exact LineString geometry object is constructed and recorded under an immutable object written to the File Store for Paths. If a Path within the Course changes, a new Path will replace the old one.
  • It would be possible to store a score for the Path along with the LineString, although applying Profiles would require recalculating that score from the underlying Segments.
  • There may be more than one Path between two locations. These are recorded as an array of Path IDs.
  • There may be more than one "Next Location" for a given location.
    • This array is dependent on the "Loop" — particularly the original departure location and other location selections along the way (will we hit a market before arriving at the picnic destination, for example).
    • It seems whatever divergence of path choice occurs, the constraint to close the loop will provide convergence — fewer and fewer route options will remain available.
    • Choices will be easier to provide and sequence if they are similar in type. Public Art should be easy to accommodate, for example, as way-points on our way to a final destination. Two parks would provide choices for a picnic location given the market has already been reached.

List of Course Pages in Word Press

  • From the WordPress console, select pages with the category "Courses".
  • From the database, run this query
SELECT * FROM `wp_posts`as p 
  join wp_term_relationships r 
    on p.id = r.object_id 
 where r.term_taxonomy_id = 27
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License