MeshDB Schema Design

Background

MeshDB is an under-development software application with the goal of replacing the New Node Responses Google Sheet (the spreadsheet) as the source of truth for NYCMesh member, install, geolocation, device, and connection information via a proper SQL database. It is built in the Django ORM, using Python Model objects to represent underlying database schema structures. The schema used for development up to this point is unable to faithfully represent some edge cases that occur at atypical NYC mesh sites. In this document, we propose a modified schema and explain each edge case, detailing how the edge case will be represented under the proposed schema

The Schema (Simplified)

The following diagram depicts the proposed schema, showing the relationships between models (SQL tables), and some key attributes of each model. For clarity, non-essential attributes are omitted (see appendix A for a comprehensive diagram).

We propose the following models:

1. Member - Represents a single NYC Mesh Member (even if they have moved between multiple addresses and therefore have multiple installs). Tracks their name, email address, and other contact details

2. Install - Represents the deployment (or potential deployment) of NYC Mesh connectivity to a single household. This most closely maps to the concept of a row in the spreadsheet. Tracks the unit number of the household, which member lives there, which building the unit is located within. It is keyed by install number, which corresponds to row number on the spreadsheet. With foreign keys to Member, Building, and Device, it acts as the central model, tying the entire schema together. Many objects have a status field, but the install status field maps most closely onto the status tracked in the spreadsheet today. Completed Installs have a foreign key to the device field (via_device) which keeps track of the device they use to connect to the mesh

3. Building - Represents a location in NYC identified by a single street address (house number and street name). In the case of large physical structures with more than one street address, we will store one Building object for each address that we have received Install requests for. Buildings track a primary network number, to represent the way the site is referred to colloquially. In the case that a building has more than one network number, the primary network number will be set to the one volunteers designate as the “primary” (usually the first assigned, busiest router, etc.)

4. Device - Represents a networking device (router, AP, P2P antenna, etc.). Most closely corresponds to a “dot” on the map. Not comprehensive of all devices on the mesh, only those that need a map dot. For big hub sites, this may be only the core router. Contains a mandatory field for “network number” (NN) which will be set to the NN of the device, or of the “first hop” router used by this device (for devices like APs which have no NN assigned). It contains optional lat/lon override fields, which can be used to refine the exact location of this device (e.g. for map display). When no lat/lon are provided for a device, is it assumed to reside at the lat/lon of the building it is associated with (via the Install model). Devices can optionally track which install delivers them power, via a powered_by_install foreign key to the Install model, which tells us which unit has the PoE injector.

   1. Sector - A special type of device (using Django Model Inheritance to inherit all fields from device) which adds additional fields related to the display of sector coverage information on the map (azimuth, width, and radius)  

5. Link - A connection between devices, which represents a cable or wireless link, whether directly between the devices or via other antennas not represented with their own device objects

Example 1 - NN492 - Typical Multi-Tenant Install

In this simple example, we have two tenants in a single building with a single address, both connected via cables directly to an omni on their shared roof. They are connected to the rest of the mesh via an LBE to Saratoga. The database tables for this scenario look like this: