I'm making a simple airline reservation system. But I'm not sure about the design decisions I made. Concretely, I'm trying to implement following constraints:

  • each Aircraft can have one or more TravelClasses
  • each Aircraft has many Seats.
  • each Flight has only one AircraftInstance (of Aircraft)
  • prices for Flight differ for TravelClasses that are known through AircraftInstance assigned to Flight
  • Client must know which seats are available for Flight booking and a price of the seat

What I have for now: database schema

Please, leave suggestions. Thank you!

  • This Q & A site is intended for clearly defined questions. You need somebody to do work for you. Maybe codereview.stackexchange.com might be a better place. I would also consider the option of paying somebody to work for you. – Erwin Brandstetter May 11 '14 at 22:36
  • 1
    Unless you're doing this as a limited academic exercise, your model is much too simple. Flights can have multiple segments for example, e.g. one flight might be YEG-YYC-LAS and passengers. There may be passengers flying YEG-YYC and YEG-LAS and YYC-LAS, all on the same plane under the same flight number. Also, this same plane may be flying under multiple numbers because of code-sharing agreements where partner airlines both sell seats on the same flight. There are many other examples of how this model is not representative of real-world commercial airline scheduling. – Joel Brown May 12 '14 at 3:07

Two general principles I would commend to you. One - name your relationships so you understand why one entity type connects to another. You may find there are more than one relationships between any two, or that some you have imagined has do not represent anything concrete in the user's world. Two - imagine some of the uses cases for this system. Think about the queries you would need to write to fulfill each. Walk through your data model to ensure it supports your queries and constraints.

The data model posted encodes many, many more rules than those you have listed. It would be impossible for a third party to validate the model in its entirety without all the rules. This is a large task which would typically take some days in a room with user representation, in my experience.

| improve this answer | |

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