Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
1  
If you laid out your data elements and use normalization to the 3rd form, you will reach to your solution, everytime. –  galuano1 Apr 18 '13 at 3:23
add comment

migrated from stackoverflow.com Apr 18 '13 at 6:32

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
MySQL doesn't enforce CHECK() constraints. Sometimes, you can replace a CHECK() constraint with a foreign key constraint. (But not this time.) The only practical way to implement "arrival city is different from departure city" in MySQL is with a trigger. –  Mike Sherrill 'Cat Recall' Apr 19 '13 at 23:35
add comment
  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.
share|improve this answer
3  
2. How else are you going to force the constraints that only certain value from Booking Status table can appear in Booking table ??? –  newprint Apr 18 '13 at 5:42
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.