Unsure how to implement certain constraints and relations in this relational database schema

Question

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench)

So far, I came up with the following design:

Here are couple things I can't figure out:

1. Is it good idea to have Foreign keys as non-int values (such as Varchar) ??
2. Database needs to somehow keep track of Number of seats booked & Number of passengers on board for particular flight. Have no idea where to place those attributes.
3. How to make sure that Arrival City and Departure City (from Flight table) are different for particular flight ?

Answer 1

1- Prefer int. Firstly it is better to have keys with shorter lengths. And later if you update a flight status, you can.

2- You can have TotalSeats in Airplane table and perhaps write an SQL query that by examining Status, you can figure out booked seats and passengers onboard. (Consider schema improvements as suggested by @galuano1
3- You can write a trigger that makes sure they are not same, or perhaps validate it in the code.

Answer 2

You get what you pay for on SO. The advice against using natural keys (names instead of numbers, in this case) is common, but misguided. If you don't believe me, ask your professor.

The number of seats would seem to be a property of the plane, right? The number of booked seats would seem to be the count of seats booked, grouped by flight number.

The arrival and departure cities can be constrained with a CHECK constraint. You might also want to ensure that departures happen before arrivals (assuming 21st century airport).

Your design is pretty good, better than some of the advice you're getting. Stick with it, don't worry about datatypes for your keys, don't look for fanciness like triggers to implement application logic (and don't fob off real constraints to the application). Do RTFM for CREATE TABLE several times. If you think of a constraint that can't be declared, I suggest you document it in your project as a flaw in the DBMS.

Do think very carefully about what's unique. I doubt you need BookingID, because PassengerID, Flight#, and Seat would seem to be unique. Passengers are complex (in a real database you'd have a bunch of tables to describe someone) so they need an ID for convenient reference, but still you likely wouldn't want two records sharing name & address.

Answer 3

1. Varchar as foreign key : Bad idea
2. There is no point in having a table for just one column.(Booking Status)
3. Remove flight date and time from flight table, create a new table called flight_schedule, put it there.
4. Remove flight no foreign key from booking, use the aforesaid flight_schedule primary key as the foreign key.
5. Then use the flight_schedule key to find out no of seats booked from booking table
6. You can create a trigger to make sure the cities are different, but you can do that in application layer as well, because triggers are bad.

Answer 4

1) Keep the PK columns as int, with no business meaning, and also have a unique column with business meaning. This means FKs should beint as well. example:

 Flight Class
   class_id int PK 
   class_name varchar(15) Unique

2) You already have Flight, Booking and Booking Status tables with enough information to derive number of bookings and passengers, so there is no need to store these as separate columns.

3) To keep the Arrival/Departure data consistent, I would create a Route table with that data only and link to the Flight table; then create an insert/update trigger on Route table to make sure Arrival/Departure Cities are different.