need for a route entity/table/class

[suekar meredilko]

did any of you ranchers feel the need for a 'route' table besides just flight and segments. My entity model seems to be falling apart without a route table.

My assumption is that
---------------------

for a route-->there are segments and for each segment there is a flight.
Without a route table, flights and segments cant be correlated.

An iternary is an association of a person interested in travelling on a specific route. It wont exist until a potential customer saves a new iternary for himself. The iternary is associated to the route after all. I have seen previous posts here but there was no reference to routes. Most seem to refer to using just a combination of flights and segments and ofcourse planned iternary. There is no info on routes, which lead me to believe that may be I am wrong somewhere.


I would like ranchers opinion on this post, whether my above comment on having a route table is correct.

[George Willy K]

I feel your thought about route is valid. Route can relate origin and destination either through direct flight or through connecting flights. There should be an admin module which can create the flights/routs etc? Any thought?

[suekar meredilko]

for folks like me who just woke up now to the exact meaning of layover..and how different it is from Segment.

pls read this url, look for Deepak Pant's thoughts. I agree with him. Ofcourse this is an old post.

https://coderanch.com/t/153028/java-Architect-SCEA/certification/segment-flight

[Rama Bhargav]

for a route-->there are segments and for each segment there is a flight.
Without a route table, flights and segments cant be correlated.

My initial design included "Route", but the more I thought about it, I realized Route is nothing but a segment which has one or more than one segment. For example, Consider the Route NY-->LA.
1) This route has two segments NY-->Dallas and Dallas-->LA.
2) The same route if flown directly has only one segment NY-->LA.

This relation can be accomplished if segment table references itself for sub/child segments. Like in Employee table, each employee can have a manager who is also an employee. In Segment table, each segment can have a reference to all feasible child segments. This would eliminate the need for a separate Route table. This is my current understanding of the Route and Segment. Hope this helps.

[suekar meredilko]

Originally posted by Rama Bhargav:


In Segment table, each segment can have a reference to all feasible child segments. This would eliminate the need for a separate Route table.

how can you store 1-m relationship without adding a new table. I've thought about it...now ..so are you assuming that each segment will have just 1 parent segment. There can be more.

e.g. Say the segment is CANBERRA - NEW YORK, which is broken into 2 more segments and since all are into a single table, it will look like
CAN-NY
CAN-TOKYO
TOKYO-NY

But question is if there are additional segments e.g HK-NY containing HK-TOKYO and TOKYO-NY, then how will you represent that ?

CAN-NY
CAN-TOKYO
HK-NY
TOKYO-NY (this has to reference both can-ny and hk-ny)
HK-TOKYO

How can this be represented with just one table. Relationships are required to show these layovers. But you can always state that as an assumption. My question is with 2 tables seems not possible unless you have a column which takes multiple values or something like that making it bit wierd

[Rama Bhargav]

how can you store 1-m relationship without adding a new table. I've thought about it...now ..so are you assuming that each segment will have just 1 parent segment. There can be more.

No, I did not assume each segment will have just one parent segment. I was only trying to make the point that you do not need the DB table/entity concept called "Route". Since each segment can have multiple parent segments, this leads to many-many relation of Segment table to itself. We would need to resolve this with association table.

How can this be represented with just one table. Relationships are required to show these layovers. But you can always state that as an assumption. My question is with 2 tables seems not possible unless you have a column which takes multiple values or something like that making it bit wierd

As you rightly pointed out, we can not achieve this with just the segment table. I am thinking of SegmentRelation table which will have segmentID and the corresponding parentID columns. So, we would end up with the following DB tables
1) Segment
2) SegmentParent(Association table for Segment as each segment can have more than one parent segment)
3) Flight
4) SegmentFlight (Association table for Segment and Flight as they are in many-many relation)

When it comes to modelling your domain classes, you do not need to introduce additional entities other than Segment and Flight to satisfy the above requirement.

[veena madhukar]

Isn't that happening by Itinerary table. The way to read is...Any Itinerary will be composed of 1 to many segments. Every segment will have a flight associated with it.

[Morten Franorge]

The concept of leg isn't in the BDM, which I find strange. Whenever I have to fly in to legs, I get a itinerary with two different flight numbers. The check in counter can however check me in all the way. I think this is how the 90% of airlines do it.
[ September 07, 2006: Message edited by: Morten Fra Norge ]