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 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

share|improve this question
    
As alluded to in chat, this question is far too broad to ask the internet and get a useful answer - but if anything, your ERD is too simple not too complex. You either need to hire an expert (of which we have several kicking around here), or spend time learning to model data and then some time nailing down your requirements. –  Simon Righarts Mar 8 '12 at 8:11
    
Why do you think it is too complex? –  FrustratedWithFormsDesigner Mar 9 '12 at 15:25
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
1  
More: in real life, a reservation is just "reserved 3 seats in Flight OA357 Athens-London, '2012-04-17 14:30' for passengers A, B and C" (and maybe without actual names first). Then a day or two before the flight, (and a specific airplane has been decided), I can check-in (choose seats). –  ypercube Mar 8 '12 at 7:59
    
@ypercube: Thanks. We've already added more options in chat too (chat.stackexchange.com/rooms/179/the-heap). This Q is way out of scope for the Interwebs... –  gbn Mar 8 '12 at 8:05
    
Actually, choosing seats can be done any time from booking the flight to check-in (which can be as little as an hour before the flight). If you support overbooking or flexible/standby bookings it gets hilariously fun fast. –  Simon Righarts Mar 8 '12 at 8:06
1  
Agreed that this is much too complex a question IRL. Bookings can have multiple passengers each with multiple itineraries any of which could have multiple legs flown on distinct equipment operating under multiple flight numbers/code shares and containing many classes of service. All of that happens before anybody starts reserving seats or checking in! It's no wonder air travel reservation systems were built on mainframes 40 years ago and nobody's dared to touch them since! –  Joel Brown Mar 9 '12 at 19:59
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.