So I have the following (classic) example to depict in an ER model:
Each airline has its own IATA code that uniquely identifies it.
An airline can configure information such as its website, travel guidelines and policies.
Your database should also contain a list of all airports (identified with the IATA code) and their location.
Each airline offers multiple flight routes that start at a depart and arrive at an airport. Flight routes have flight number (which is reused). Each individual flight departs and arrives at a specific date and time. They have a set of options that can be booked, such as (but not limited to) check-in luggage, pets or meals. These options and their price can be configured by airline users. Each flight has a price per person and per class (e.g. economic, first class, business class). This price can vary.
Every flight number is bound to a specific aircraft type that has a limited amount of passengers in
The database should contain different types of users on the website: moderators who verify that
all information is correctly entered, customers who can book flights and airlines who can add new flights to the system or modify existing flight information and options. An airline user can modify the prices of listed flights and options. Prices can change often (e.g. due to events), but this shouldn’t change the price for customers who already booked a flight.
Customers can book one or more (connecting/return) flights for themselves or other passengers.
Each passenger should be identified with a name and valid passport number. For every flight the
customer should specify the class and options.
Here’s what I have. But it’s one of the first times I do this and I have some questions.
I have a few specific questions but please point out anything on which I can improve. A bad ER model is a bad database after all…
-I create a loop in my ER since the airline is also a user. Is this an issue? Is there a way to avoid this?
-is using the nam “IATA code” as the primary key for 2 different entitites a problem? or should I specify “IATA code airport” and “IATA code airline”
-Can the prices of the options for flights (luggage, meals, etc in the book relationship) be shown in a better way? Now I give the customer the possibility to choose an option while booking, and have the prices as attributes of the flights (so they can be modified by the airline).
-I added a “booked flight” option to the cusomer to be able to save their price, even if the airline modifies the flight prices. Is there a better way to do this?
Thank you to anyone who takes the time to read this.