-
Notifications
You must be signed in to change notification settings - Fork 23
2. Database Overview.
Note: you can reference a map of the database architecture for MyFlightbook in SVG Form or in PDF Form.
There are a lot of tables, and I won't go into each one here, but there are 3 key clusters: Users, Flights, Aircraft.
The Users table is the hub of just about everything. While there is a unique (GUID) based PKID for each user, the workhorse column is "username", which is generated from on the initial email address used to create the account. Pretty much everything else in the database ultimately ties to a user, via this column, so you'll see this in the center of the database chart.
Flights are obviously what MyFlightbook is ultimately about; it's actually a fairly simple table. Most of the data for a given flight is in each flight row; the three exceptions are linked videos, telemetry, and flight properties. Doing the properties outside of the core columns keeps the number of flight columns manageable.
Each flight is associated with a user and an aircraft, and it is via the aircraft that each flight gets many of its characteristics. This is the most important association with other tables, and is discussed below.
The FlightVideos table simply holds references to externally hosted (YouTube or Vimeo) videos for a flight. Note that embedded videos (ones that are uploaded directly to MyFlightbook) are not stored here; they are instead referenced in the Images table.
Telemetry manages the mapping between telemetry and flights, offloading it from the main table. Telemetry files are parsed and placed as files on the disk, with the resulting telemetry record holding the path and some other details for quick retrieval without having to load/parse the full (and possibly large) telemetry file.
The FlightProperties contain all of the additional properties (fields) that a flight can have. Each of these properties is described by an entry in the CustomPropertyTypes table, which describes the data type, label, description, and other semantics for each property. There are over 500 possible custompropertytypes, with more added every so often, which is why these are stored outside of the main Flights table.
The FlightDataColumns table does not reference any other table; it is used when parsing telemetry to describe possible column names and assign semantics to them. E.g., that "Lat" describes a latitude, that "CHT4" describes a temperature, etc.
The aircraft for a flight encapsulates a lot of information about the flight, primarily via the associated model table. The model (and it's associated category/class table) determines things like the category/class of the flight, whether it was tailwheel or high performance or turbine, etc.
Every flight has an associated aircraft (e.g., N12345) in the Aircraft table; every aircraft, in turn has an associated model (e.g., C172S) in the Models table, and every model is made by a manufacturer (e.g., Cessna) from the Manufacturers table and has an associated category/class (e.g., "Airplane, Single-Engine Land") in the CategoryClass table.
So, for example, to determine if a flight was high performance, you need to look at the Aircraft table to find its model, look that up in the Models table, and then look for the high performance flag on that model.
Most aircraft in the system are real flying machines, but the associated AircraftInstanceType describes whether a given aircraft is a flying machine or one of several kinds of training devices.
Each aircraft has a tail number, which generally begins with a prefix from the CountryCodes table. This is mostly used as an aid to help people enter full (and ideally globally unique) tail numbers, but it is possible to create an arbitrary tail number (e.g., military aircraft are often simply numeric). There are two pseudo-prefixes in the system as well:
- # followed by a 6-digit numerical representation of a model ID represents an anonymous aircraft of that model. E.g., "#000409" is an anonymous Boeing 747-400 (modelID = 409).
- SIM is reserved for training devices; this works both because it is a good mnemonic and because it doesn't conflict with actual country codes.
Note that while the user interacts with aircraft by tail number, these are NOT unique - two or more aircraft can share a given tail number, for example if a registration is reassigned from one aircraft to another. The aircraft's ID is its unique identifier.
The UserAircraft table is what ties this all together for a user - it associates a given user with a given set of aircraft. This is also the means by which disambiguation is provided between two aircraft that share a tail number.
Finally, the AircraftTombstones table simply maps deleted aircraft to the replacement aircraft, since there is occasionally a need for the administrator to merge duplicate aircraft or delete unused ones.