In working on SCEA part 2, I'm trying to code some things to help me along. I'm having trouble visualizing how seats are represented in a database. When a user goes to select seats for a flight, what happens? I'm thinking that a flight record will have a type-of-equipment field. There could be a separate table for seats that might consist of flight#, type-of-equipment, seatname, seatTaken (true or false). But what is tripping me up is that it seems you would need such a table for every flight; you'd have as many of these tables as you do flights. (I don't have much database experience but that somehow doesn't sound right to me.) If that were the case, it sounds like a better idea would be to have the flight record contain a field called seats and that field would contain a HashMap (where key is the seatname and value is available or not). I'm not seeing how to store an array or HashMap in a database. What datatype would you give it when you go to create the table? Would one even do this? If so, how? And if not, how to handle the case where a database record contains a field that needs to be an array or List or Map?
Here are my thoughts on this problem. What if the database had a Reservations table that held the Flight#, date and Seat# for those seats that were already reserved? Then you only need to retrieve the seat numbers for that equipment type for that flight on that day that re not in the Reservations table. Or we can retrieve that info. from the stored itineraries since we have to persist them anyway.
IMHO, there are two sides we need to put in consideration.
One is the "flight" from airlines for sale. it contains the segment(from-to), the flight Id(an actual flight, not the flight number), the equipment Id(an actual airplane, same 767 may have different seat setting), and a set of seats(if a seat is booked, there is reference to the "booked flight" transaction record). the admin loads the "flight"s when they are going for sale.
On another side, the "booked flight transaction record" has the similar info and some thing extra.
Maybe we can use same class and DB table to keep the two kind info. The purpose of CLOB is to separate long descriptive strings to make query faster, I don't thing well use it here.