I've been trying to set up a database, but i'm affraid i'm getting lost in the complexity.
Let's say i want to document commercial aircraft.
First of all, i want to show a rough ERD i've drafted.
Of course, aircraft can be devided in helicopters and fixed wing. Aircraft come in different types, and types come in different variations.
Example: A boeing 747 (fixed wing, turbine engine, turbofan) can be a passenger aircraft or a freight carrier, and can have different types of engines.
What I want is a "master record" of ,let's say, a boeing 747 with general data like an image, wingspan, year of first flight etc.
I also want to keep track of variations with specific engine information, operators (airlines), registration numbers etc.
Airlines can be specified by alliances, country of origin, hubs (home airport)
I've tried to identify some common variables, but i'm not sure how to translate this into logical tables.
The hierarchy could look something like this:
Aircraft
FixedWing/RotaryWing
Engine type (turbofan/turboshaft/piston)
Type (example Boeing 777) (general information, with image. Must be "printable with as much general information as posible)
Version (example 777-200, Extended Range, 777-9x)
Variation/specific aircraft (with specific information: operator, specific image, registration number. Must also be printable)
Some information does not apply to all records: a fixed wing aircraft has no rotor, but might have a common manufacturer or operator. So how to make sure only the relevant fields are stored/recalled? Does this mean it should be seperate tables?
Also i want to create records on countries, airports, alliances, airlines.