-----Original Message-----
Hi Olga,
It always helps to get the real-world entities clear before getting too
deep into the tables. Here, it sounds as if the categories "airline" and
"handling company" overlap (because some airlines do all or some of
their own handling and (I expect) some airlines provide some services to
other airlines.
So there's probably just one entity, "Company", rather than distinct
entities "Airline" and "HandlingCompany", and one Companies table.
But because one company can operate at multiple locations it can have
multiple branches/departments/divisions each with its own address.
Modelling this may require two entities, Company and Branch with a
1:many relationship, or it may be better done with a self- join in the
Company table (a ParentCompanyID field so that one Company record can be
linked to the record of its parent Company).
There are Airports (unless you actually need a more general "Location"
entity, where a location may be an airport or terminal or depot or
whatever).
And there are Services. Operating aircraft like an airline could be
modelled as just another Service, but it may be simpler to keep it
separate with an AirlineActivityPerLocation table. Do you also need to
ensure that the database will reject impossible combinations (e.g.
passenger handling for a cargo-only airline)?
tblAirlineActivityPerLocation
CompanyID )
AirportID ) PK is these 3 fields
Terminal )
TotalPaxAircraft
TotalFreightAircraft
That's the easy bit. We also know that Companies operate at Airports (or
Locations!), providing Services to Companies (a Company can provide a
Service to itself). Possibly what's needed is one table like this
tblServicesOffered
CompanyID )
AirportID ) PK is these 4 fields
Terminal )
ServiceID )
Notes
to track which companies offer which services where, and a related table
tblServicesPurchased
ProviderID - FK to tblServicesOffered.CompanyID
PurchaserID - FK to tblAirlineActivityPerLocation.CompanyID
AirportID - FK to both tables
Terminal - FK to tblServicesOffered
ServiceID - FK to tblServicesOffered
to track who uses them.
I'm far from certain that this is the best structure, but hope it will
give you something to think about. Note that I haven't put articial
primary keys in tblAIrlineActivityPerLocation or tblServicesOffered,
instead relying on multi-field primary keys and multi- field
relationships between those two tables and tblServicesPurchased. The
idea is that relational integrity on these relationships will make it
impossible to store a service that is not actually being offered by that
company at that terminal, or show it being provided to an airline that
does not operate from there.
Hi David,
Thank you for replying. By the "business" I mean Ground
Handling: passenger and ramp services, cargo services,
freight and plane cleaning. Some of the larger airlines
can take care of it themselves (like Delta or United), but
many others outsource ground handling companies for these
services. Airlines can hire one company for cleaning,
another - for cargo handling and take care of passenger
services themselves. That's what the most difficult for
me: there is no clear line between Customers (airlines)
and the Handling Companies.
I want to concentrate on the locations where my company
operates (which is more than 60 airports in US and much
more worldwide, but I want to concentrate on US only right
now). Any service not provided by us is the opportunity.
With this DB I want to be able to pull info about any
airline in all locations, the number of flights per
location and who provides the services at a given point.
Also I would like to be able to do comparative research on
the competitors (market share per location in each service
segment).
I know it sounds confusing. Please let me know if you have
any questions. I will really appreciate your input. I am
relatively new to this and I was reading books on table
normalization, but I find it very difficult dealing with
intangibles here.
Regards,
Olga
the
actual data...
of the "business" is handled by the airline itself, and
some of it is handled by a "Handling Company".
By "business" do you mean flights (in which case the
Handling Company's are actually other airlines), or do you
mean "services"?
Handling Company" supplies the service?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
