Flight schedule

[t_g2]

Hello,

First of all, I am not a pro.

I am tasked with designing a database about air transportation.
It needs to store the following information:

- flight schedule
- all flights and segments
some flights stop at multiple airports [transfer flights],
all airports can be origin and destination, too
- which company is the scheduled carrier for the flight
- which days each particular flight flies (they fly only on particular days)

- actual flights
- actual departures and arrivals;
- origin, destination [this is for tracking diversions,
differences from schedule; otherwise the same airports as above]
- date and time of arrivals and departures
(some flights are overnight; queries need to show/calculate both
local and GMT times)
- actual carrier; sometimes the scheduled one is not available
- fuel and deice purchases for any particular flight segment
- delays by reason (delay code)

- fleet
- make, model, max weight can be carried by the model,
tail# and registration#

- weight packages carried
- from, to; both the same airports as above, but here, the origin
and the final destination for calculating shipping charges (shipping
cost is based on from and to, which flight is used to get it there
is irrelevant for the cost)


- how much and for which customer (also for calculating shipping
charges)

My biggest problem now is that I linked the airports to the origins and destinations to all tables where these are stored (flight schedule, actual flights and weights) and the relationships look like a mess, creates duplicate copies of the airports, provinces/states and countries table. I am not sure what I did wrong.


I populated the tables with some mock information.


All information and help is greatly appreciated

[llkhoutx]

First of all, do not use spaces or special characters in table or field names. Such characters wreaks havoc with SQL statements and upsizing tables to Oracle or SQLServer.

I would suggest of your next database, use a "PK" prefix for primary key field name and an "FK" prefix on foreign key field names. PKtalename and FKtablename tell you a lot by inspection.

Your tables and relationships are a mess, and sometimes duplicious. I doubt that all relationships are equi joins, as they eliminate lots of records where data is queried. Right joins work much better, provided you have dominant table(s) - which you do.

Duplicates records, one or more fields will not be duplicate, as the result of no or improper filtering.