Guidance on simplifying the following diagram

database-designerd

I have a case scenario where a customer can book accommodation or flight only or both.

I have modeled the accommodation part, however i am making things complex by over-thinking how stuff works in real life.

What i have thought is that a customer cannot book a flight because it is referring to a whole aircraft, very unlikely. Therefore i thought that a customer books a flight seat.

The following is what i have done so far, but i have made it too complex.

Flight

Can someone guide me to make this simpler? Could i merge flight_seat with flight and aircraft? please help

Best Answer

No, you shouldn't merge the 3 tables.

  • For a given flight, aircraft may change
  • For a given aircraft, flight may change

However, I'd suggest that your model isn't complex enough

  • FlightSeats depends on Aircraft model (seat layout etc)
  • Aircraft departs Airport too...
  • .. but Flights leave and departs Airports. Or is it FlightAirport?
  • Flight is arguably 2 entities: the base flight (number, schedule etc) and the actual FlightInstance (date etc)

Have you written down all your facts first? Say in tabular format like this MSDN example (actually for ORM which I use because ERDs don't capture all the information you need)

Edit, more...

  • As per @ypercube and @Simon Righart comments below
  • From @Simon Righart: A given aircraft might have three different classes of seats (economy, business + first) with different numbers of each each flight number might be flown by multiple aircraft
Related Question