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

database-designMySQL

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:
database diagram

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 ?

Best Answer

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.

Related Question