Passenger Seating

NathanAshton · April 14, 2009

Hi All,

I'm looking to design a database for allocating passengers to a particular seat. The concept I was thinking is having a field for each seat (4A, 4B, 4C etc) You enter the passengers details then enter the allocated seat with their name. This then copies their name across to that seat field, colors the seat in, and adds their name as the tooltip.

Is this the best way to go about it?

Cheers

comment · April 14, 2009

I believe it would be much better to have a RECORD for each seat, with a relationship to the passengers table.

NathanAshton · April 19, 2009

Thanks for the reply.

So, a seating table with Records for each seat (4A, 4B, 4C etc)?

The other table with Passenger name and a seat field.

How would I relate the two?

The concept is always the hardest part for me to work out. Once that's ok, the doing part isn't as hard.

Cheers

NathanAshton · April 19, 2009

I've just put together my concept and attached it.

You click on each seat and can allocate, change or empty the seat. It then fills the Seat Record with the PassengerID.

I'm not sure if this is the best way to go about it?

Also, if I did stick with this method, how would I fill in the Manifest:SeatNumber record as each seat is allocated?

Seating.zip

comment · April 19, 2009

I believe you need to do this the other way around, that is give a seat to a passenger, not a passenger to a seat. Otherwise this will work for only one flight. This assumes that your Pasengers table is actually a Tickets table (i.e. repeating passengers get a new record every time they fly).

You should also make sure that the combination of flight/seat is unique in the Passengers table (and build a GUI that will make it easy to accomplish this without the validation kicking in).

Unless I am missing something, the manifest is just a list of passengers of a flight, with the related seat number placed on the same layout.

NathanAshton · April 21, 2009

You're right. The most common sense approach is to allocate seats to passengers not the other way around.

I've messed with my scripts and almost have it working. There is an issue however with one of the Pauses that I can't work out. If I step through the script it works ok though.

Once the seat number has been allocated to the passenger, I assume I'm going to need some sort of relationship to allow the Seat to show who occupies it?

Thanks for your continued help.

Seating-1.zip

Ocean West · April 21, 2009

http://video.google.com/videoplay?docid=7747446916663791334

Check this out...

Edited April 21, 2009 by Guest

NathanAshton · April 21, 2009

Thats a pretty cool video. The Aircraft Seating demonstration is exactly what I'm after.

Would be nice to find a copy somewhere but I'm guessing it's owned by somebody else :o

comment · April 21, 2009

I don't think we are talking about the same thing. In terms of user interface, it is merely a matter of convenience whether you assign seats to passengers, or passengers to seats.

However, in terms of the data model, there is a big difference between having a PassengerID in the Seats table vs. having a SeatID in the Passengers table. When I said "give a seat to a passenger" I meant have a SeatID in the Passengers table. IOW, the relationship is one seat to many passengers.

Also, each seat should have its own record, with a unique SeatID, in the Seats table.

NathanAshton · April 21, 2009

I think it's the beginner in me but I just can't wrap my head around the data model.

Are you able to show me a sketch or similar?

Cheers

Edited April 21, 2009 by Guest

comment · April 21, 2009

The basic structure is:

Flights -< Passengers >- Seats

IOW, the Passengers table is a join table between Flights and Seats, and it needs two foreign keys: FlightID and SeatID.

NathanAshton · April 21, 2009

Thanks, I'll give that a go

NathanAshton · April 21, 2009

Am I on the right track with this?

Obviously I'd need to work out how to deal with duplicates / seat changes / deletions etc.

Cheers

Seating.zip

comment · April 21, 2009

Am I on the right track with this?

Almost, I think. The FlightID in Seats cannot do anything - unless you intend to generate all seat records for each and every flight. I would make it a global field, so that you can select a flight to view from the context of Seats - see attached.

Seating.fp7.zip

NathanAshton · April 24, 2009

I've added in the ability to assign passengers to Sectors. I think the way in which the data is now stored is solid (or close to it).

I'm now looking at a way to highlight seats on the seating plan that are occupied. This is the method I'm thinking:

Do a find for all passengers on that sector. "Loop" through all these found records and record the seat number into the field "seatallocations" (so field ends up as 4A 4D 5E) etc.

The seats on the seating plan are conditionally formatted to highlight if "seatallocations" contains their seat number.

Close? or no cigar lol

Seating.zip

comment · April 24, 2009

What is a sector in real life? Isn't it an attribute of a seat?

As for highlighting, I don't think a script is necessary. A seat "knows" it's occupied, because a related passenger record exists. However, this requires the global gFlightID field in Seats, in order to look only at passengers of the current flight.

NathanAshton · April 24, 2009

Sorry but I just can't get it to work properly.

Seats are allocated ok, but I can't work out how to display allocated seats on the seating plan. I've tried using conditional formatting with no luck.

Any chance you could upload a sample?

Cheers

comment · April 24, 2009

I could add this to my earlier one - but I'd prefer knowing what exactly is a sector first.

NathanAshton · April 24, 2009

Sure.

Each flight can have one or more sectors. eg Flight 123 can operate A-B-C-D. Pax can board in any location and head to any onwards destination.

So, I'm basically allowing for passengers to travel A-B or A-D or C-D etc.

Hope that makes sense.

Cheers

comment · April 24, 2009

See if this moves you forward.

Seating.fp7.zip

NathanAshton · April 26, 2009

Thanks for the sample. Definately moving forward now!

It never occured to me to use Portals for the seats. Works really well.

I have attached the database again, with lots of improvements I have made. One small thing has me hung up again. If you click on a seat it brings up a custom dialog and tells you if the seat is empty or who occupies it if it's not empty. This is processed by the script "SeatViewer - Click on Seat".

Per the attachment, ASY800 has seat 4G allocated. ASY700 has seat 5G allocated. If you go to ASY800 and click on seat 5G it says the seat is allocated when it isn't. My script is checking for all flights not just the selected flight.

What do I need to change to the script to get it to check if the seat is empty or occupied ONLY for the current flight?

Cheers

Seating2.zip

comment · April 26, 2009

Open the relations graph, imagine yourself in the Seats table and ask yourself: "What do I see from here?".

If you look into the Passengers TO, you see all passengers that have ever been assigned to the current seat.

However, if you look in the other direction into Passengers 2, you'll see only passengers that are assigned to the seat for the current flight - because that relationship is filtered by gFlightID.

Therefore, your test needs to be:

not IsEmpty ( Passengers 2::PassengerID )

NathanAshton · April 28, 2009

Is there an alternative to using portals to display the seats?

It is working well, however with 300+ records the portals display very slowly. I've looked at Flash & XML but that seems to require FM Server. Also had a look at the Fusion Reactor plugin but that seems quite in depth.

Fenton · April 28, 2009

It seems pretty fast to me. One thing that contributes to a layout seeming slow is that FileMaker normally draws objects in the order that they were created, kind of like layers of objects. If you're done messing about with the portals, select them all, Group them, and choose Send to Back. That causes them all to be drawn at once. It may not make it any faster, but it seems faster; less annoying anyway.

Søren Dyhr · April 28, 2009

I think I would cut up the portal more drastically instead of masking where the aisles should be, the number of conditional formatting will then in this case be lessened by some 90 renderings, further more will clicking and falsely assigning to non existing seats be prevented.

--sd

NathanAshton · May 2, 2009

Here's another interesting question.

I would like to know how many people are on the aircraft when it departs a particular location.

The problem is the aircraft is flying route A-B-C-D. Pax are able to board in any location and exit at any following location. e.g A-C or B-C.

So in the above example, as the aircraft departs B there are those with B as the boarding sector, and those who boarded in A, continuing on to C or D.

How would I calculate the number of people as the aircraft departs each port?

Cheers

comment · May 2, 2009

Try something like this, perhaps?

Seating.fp7.zip

NathanAshton · May 2, 2009

Cheers,

I can't seem to get your example to work. I have attached something more similar to my database so you can see my dilemma.

I was hoping a Calculation field would be able to work it out, but not sure really.

Seating_Demo.zip

comment · May 2, 2009

I think you need to figure out - in terms of business rules - what information needs to be entered, in which order, and what can be figured out on the basis of that. At least in theory, a flight could go A - B - C - B - D, and if a passenger bought a ticket from A to B, there's no way to tell when exactly they will depart.

Note that you are at a point where you will have to break one of the rules of a relational database: some of your child records will have to become an ORDERED SET.