Subtable

[Marek Staniewski]

I have some problem to organize some complex table.

Let say there is a table with a number of records.
I am looking for a way to arrage that some fields may contain not one value
but a list of values.
In other words I am looking for a way to insert into the table a subtable
or something like this.

Let me try do describe the particular problem.

I shall construct a table containing information about fly schedule of
different airlines

In a table records the following fields should be insert:

1. Airline name (from separate (related) database)
2. City name from (departure): (this also should be form airport list
database
3. City name to (arrival) (this also should come from separate database)

So far it is easy.

But now the problem comes:

I have so far described the route for a particular airiline.
Now each route may be described quite comlex way:
Fo example The particular airline on the route from New York to Paris is
travelling:
every Monday at 8AM (except - here list of exception - for example
Christmas etc)
Every Tuestday at 2PM (except - here list of exceptions)
In addition there is a list of irregular flies (date and time)

I suppose it is silly to build a table for each particular route. There
should be some much simpler way to describe such a relations.

Anybody can help?

Marek Staniewski

 

[Wayne-I-M]

Hi

We already have something like you discribe. We use it all the time to
prepare flight reports.

If you look at
http://www.ukoutdoorpursuits.co.uk/trek_dossier/web_Trek.pdf

You will note that some of our clients depart from (one of many) UK airports
and go to Peru (Lima). Some of them return and some of them come via Quito
(Equador) and then some of those depart for home (via Amsterdam) and some of
them come home via (Bonaire - Carribbean and then Amsterdam).
So - yes you do need to enable the same departure and return coty but via
routes .

The way we have it (there may be other - better, methods) is to use 4 tables
- our clients are allocated a "route". Your route table should have many to
many relationships with the other tables. Then you can allocate any clients
onto any route. Or, in your case you can produce reports.


tblFlight
FlightID
FlightNumber
DepartTime
ArriveTime

tblAirline
AirlineID
AirlineName
AirlineTaxStatus

tblAirport
AirportID
Country
City

tblRoute ( this is your many to many table)
RouteID
BookingID
FlightID
AirlineID
AirportID
Add the linking fields from the other tables to this and then allocate
people on to a route.

The BookingID is the main link to the clients specific booking as many
people take part in more than one trip

Each person has 2 Routes - one out and one back. You could create different
tables to cope with another method, but this system works for us as it allows
people to depart via a different route than that which they return. This may
not be required for your use.
Note that the costs are allocated in the the booking table as they are not a
"direct" cost and so the tables above don't link to the flights costs.

The above table structure allows the same flights to be added to diffeent
routes.
eg.
Route 1 departs from airpoirt 123 on flight XYZ to airport 456 with no stops
Route 2 departs from airpoirt 123 on flight XYZ to airport 456 via airport
789 on flight LMN with 1 stop
So you can see that both routes use flight XYZ

Hope all this makes sence - if not, post back