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