Database

Database Diagram

Conceptually, the schema is broken down into the following groups:

Group	Contents
Sighting	Main data tables used for holding sighting details and related information
Airport Reference Data	Register of valid airport IATA codes, used for input validation
Ancillary	User authentication table, background job statuses and EF Core migration history
Keyless SQL Reports	Empty tables generated as an artifact of the SQL report configuration

The "Sighting" group consists of the following sub-groups:

Sub-Group	Contents
Aircraft Information	Aircraft details and associated models and manufacturers
Flight Information	Flight start and end points and operating airlines
Locations	Named locations where sightings are made
Sightings	Records of specific aircraft on specific flights at specific locations

Aircraft Information

Each aircraft is associated with with a named model that, in turn, is associated with a named manufacturer. Multiple aircraft may be associated with a single model and multiple models may be associated with a single manufacturer:

Table	Description
Manufacturer	Named manufacturer details
Model	Named models, each associated with a manufacturer
Aircraft	Aircraft registration details, each associated with a model

Location Information

Sightings are made at named locations. Multiple sightings may be associated with a single location:

Table	Description
Location	Named location details

Flight Details

Each flight has a number and route and is operated by a named airline:

Table	Description
Flight	Flight numbers, points of embarkation and destination airports
Airline	Named airlines

Sighting Details

Each sighting is associated with a location, an aircraft and a flight and has additional details such as the aircraft altitude at the time of the sighting and the date:

Table	Description
Sighting	The sighting records

Security Implementation

The registered users for the application are held in the User table. The contents of the table are used to authenticate users via the REST API.