I have a project that I’m working on and I am getting stuck.
I currently have a table that contains flight information. Each row is one “trip”. It can have up to six airports (one arrival, one destination and two layovers each way). I have a way to grab all the data for the airports (lat and long, city name etc) and I use that for other calculations/views. Right now I’m trying to come up with a way to show all the airports for a particular trip on a map but I know that a map is a container and can only look at one column at a time. My question is, how do I transpose or somehow otherwise modify the data for a flight (probably with the help of a “helper table”) so that I can show up to all six airports on a single map.
Bonus is if I can then additionally have another column that contains all of the unique airport codes so that I can show a separate map with all the airports/city that I have flown through in my life.
This is similar to the Flighty App on iOS and a bunch of other options both on iOS/Android just that this has a lot more customizations in it for my specific use.
If rows count isn’t a concern, I would have a Trips table and a Trips x Airports table.
1. Trips Table
This table will store the basic information about each trip. Each row represents a single trip.
TripID |
TripName |
StartDate |
EndDate |
1 |
Summer Vacation |
2023-07-01 |
2023-07-15 |
2 |
Business Trip |
2023-08-10 |
2023-08-12 |
2. Airports Table
This table will store information about each airport, including latitude and longitude for mapping purposes.
AirportCode |
City |
Country |
Latitude |
Longitude |
JFK |
New York |
USA |
40.6413 |
-73.7781 |
LAX |
Los Angeles |
USA |
33.9416 |
-118.4085 |
LHR |
London |
UK |
51.4700 |
-0.4543 |
CDG |
Paris |
France |
49.0097 |
2.5479 |
NRT |
Tokyo |
Japan |
35.7647 |
140.3863 |
3. Trips x Airports Table
This table will act as a junction table, linking each trip to the airports involved. Each row represents a connection between a trip and an airport, along with the sequence of the airport in the trip.
TripID |
AirportCode |
Sequence |
1 |
JFK |
1 |
1 |
LAX |
2 |
1 |
NRT |
3 |
1 |
LAX |
4 |
1 |
JFK |
5 |
2 |
LHR |
1 |
2 |
CDG |
2 |
2 |
LHR |
3 |
Example Data
- Trip 1 (Summer Vacation):
- Sequence 1: JFK (Departure)
- Sequence 2: LAX (Layover)
- Sequence 3: NRT (Destination)
- Sequence 4: LAX (Layover)
- Sequence 5: JFK (Return)
- Trip 2 (Business Trip):
- Sequence 1: LHR (Departure)
- Sequence 2: CDG (Destination)
- Sequence 3: LHR (Return)
Then, in the Trips table, you can create a relation and query back all airports in each trip to display on a map (with their coordinates).
1 Like
I like this solution but I’m not sure it will work. One of the calculations that I do is to calculate the distance between the different airports. Right now I have a bunch of checking logic to figure out if a flight is direct (and the distance is just between the departure and arrival destination) or if it is a flight with one or two layovers and likewise if the outbound flight has a layover but the return does not.
I do think that you gave me an idea how I can possibly use the linker table so when a user enters the flight data on one table it will also enter the
I think the comment got cut off halfway. Can you check and present your idea in full?
Yes the comment cut off. Thank you for pointing that out.
I basically decided that the app will have limited (albeit still very broad) coverage. I can only have a departure airport, arrival airport, and two layovers each way. This was what I had described in my initial comment but I was thinking I might want to expand it for future use (multi-city flights where you don’t have a fixed destination, think around the world in an extreme case).
With that in mind I set up a helper table. This would contain the airport name, user (who flew the flight) and the flight ID(a unique ID). From there I created a new workflow that would be triggered when the user entered the flight information.
Note that the type of trip is selected on one page (right now the only options are one way or round trip). They then select a button that opens a new page with multiple text entries for the different types of flights (If the user selected round trip then it will show both outbound and inbound entries, but if they selected one way then it only shows outbound entries). This is all controlled with conditional viewing options. The user then enters all the airport choices, which are logged in a temporary table for the time being. If the user actually wants to save the flight (ie. log it and not just calculate distance) then there is a button which triggers the aforementioned workflow. There was no easy way of doing this without potentially saving a number of blank cells (at least none that I could think of), so the workflow has conditional statements (9 I believe) to check what cells actually have airport codes in them and then depending on what cells are populated it writes those airport codes and the trip ID to the airport helper table with each airport being a new row. This then allowed me to map all the airports that were associated with a trip ID on the trip summary page.
I still need to put in a little more error handling to basically not write the airport data twice to the helper table. I also want to put in some more “safety” logic so that I don’t accidentally overwrite data that I want saved.
I apologize if my explanation is a little rambling or unclear, I’m an engineer with no formal training in software development. I just have an idea and will brute force it into happening even if it’s kind of ugly and clunky.