Table Normalization-Please help

[Olga]

Greeting Everyone!
I am trying to develop Marketing DB (Market Share) for my
company. Airlines (customers) have certain amount of
flights per location (airport/terminal). Some of the
business is taken by other companies (Handling Companies),
some of it is self-handled by an airline itsself. I would
like to be able to consolidate the information about
available business per airline and the competitive
information. Could you please review my table structure
and let me know if it looks ok. Thank you!

AirportTbl
AirportID
AirportName
AirportDetail

AirlineTbl
AirlineID
AirlineName
AirlineDetail

ServiceTbl
ServiceID
ServiceName

HandlingCompanyTbl
CompanyID
CompanyName
Address
City
Region
PostalCode
Country
Phone

AirlineActivity per LocationTbl
AirlineActivityID
AirlineID
AirportID
Terminal
TotalPaxAircraft
TotalFreightAircraft
Note

HandlingCompanyBusinessTbl
AirlineActivityID
CompanyID
ServiceID

 

[David]

Hi Olg

This is quite challenging without knowing more about the actual data..

Is a "Handling Company" an airline itself? You say some 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"?
What are the "services" referred to? I assume the Handling Company" supplies the service

I think this information would help with the data modelling..

Let me kno
Davi

 

[Olga]

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

-----Original Message-----
Hi Olga

This is quite challenging without knowing more about the actual data...

Is a "Handling Company" an airline itself? You say some

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"?

What are the "services" referred to? I assume the

Handling Company" supplies the service?

 

[John Nurick]

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.