Need help designing a database

[Aviator]

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.

[NTC]

will you need list/static tables for look up - and you need what is called a transaction table that is the intersection where all things are put together.

In analogy is street addresses; there are numbers, street names, city names, state names

one should have a table for states, a table for cities.... the idea of a table for street names could be or perhaps there are too many... and it is probably not practical to attempt a table with all numbers

so then you make the table 'Addresses' with a field for number that is free form typed in, the field Street can be a look up of the Street table, the field City is a look up of the City table, the field State is a look up of the State table

I use this analogy to show that there is some decisions based on your insight to your business. You need to determine which sets should be lists tables to be looked up...and in the end you have the master table that pulls it all together.

[orange]

aviator,

You may get some ideas for your hierarchy

Some information does not apply to all records

from these links/articles.

The topic in modelling terms is supertype/subtype
1.Dr Daniel Soper database series-- subtype/supertype at about the 44 minute mark--data modelling
2.Generalization
3.see page 199+ in this article
4.subclassing at MVPS.org

A good tutorial for database design concepts is this one from RogersAccessLibrary. You have to work through the tutorial (30-45 minutes), but you'll learn, and what you learn can be used with any database. It has a guide and a solution. Probably less time since you have created a data model/ERD.

Good luck.