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 or "own" multiple active 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:

Example 2 - NN 4734 - Cross-Building Installs

In this example, members in 3 adjacent buildings, each with their own address, are connected via a single omni, with cable runs across the roofs directly to the member’s apartments. They are connected to the rest of the mesh via an mant 802.11 sector at 4507. The database tables for this scenario look like this:

Example 3 - 7th Street (NN 731) - Multiple Omnis on one building

In this example, we have one regular tenant in a single building with a single address. However there is also a rooftop office with its own omni, connected wirelessly to the primary one. They are connected to the rest of the mesh via a GBELR to Grand. The database tables for this scenario look like this:

Example 4 - Vernon (NN 5916) - Courtyard APs

In this example, we have a core hub site in a single building with a single address. However, there are many Access Points (APs) on light poles in the building’s courtyard. These light-poles are unquestionably associated with the same building/address as the core router of this hub, but need to be shown separately on the map. 

In this scenario, we treat the light poles as if they are “apartments” in the Vernon building. They each get their own install #, but imagining a tenant living in the light pole, we say that this imaginary install is “connected via” a device object representing the AP. The network number for these APs is set to 5916, reflecting their first hop router (and the fact they are not themselves assigned NNs). Links between the courtyard APs and the core router are included so that they are rendered on the map  

The database tables for this scenario look like this:

Example 5 - Prospect Heights (NN 3461) - Multiple NNs for one building

In this example, we have a core hub site in a single building with a single address. The primary NN 3461, also serves a member’s apartment as install #3461. However, there is another apartment which could not due to practical considerations be connected via a cable, and had to be connected via an antenna in their window to a sector on the roof. This antenna needed an NN for configen and naming, and so this building received multiple NNs.

The database tables for this scenario look like this:

 

Example 6 - Jefferson (NN 3606) - Multiple NNs for multiple buildings

In this example, we have a building with 4 addresses and 3 omnis on the roof, each with its own network number. There is no clean mapping between NNs and addresses, since each omni serves installs in multiple buildings. The omni of the primary NN, 3606, provides the uplink to Hex House (NN 1417). 

The database tables for this scenario look like this:

Appendix A - Full Schema Diagram

The following is a complete schema diagram, showing all fields. New additions from the current implementation are shown in yellow, and removed fields are shown in red