How much it TOO much?

K

Khazel

I am trying to determine the best possible database structure for our
product. I will give a very crude example of the problem I am facing, along
with 2 possible scenarios. I am not sure which is best to use at this
point, or if I am overdoing it with my application of data normalization.
Thanks in advance to anyone that can help!

Database would be for an airline; Airlines travel between cities,
associated with specific departure numbers with data for each date.
(example: Dep#100 from DFW to LAS on 10/10/03 leaves at 10:00am is leg 1,
Dep#100 from LAS to LAX leaves at 2pm and is leg2.) each with multiple sets
of fare class data associated to them.

SCENARIO ONE (more strict normalization):

CITYPAIR Table
CityPair -primary key
Origin City
Destination City
Distance

LEGS Table
LegID -pk
DepartNo
CityPair -fk to CityPair table
LegOrder

DEPARTURES Table
Dep_ID -pk
LegID -fk to LEGS Table
DepartDate

BOOKINGS Table
Dep_ID - fk to Departures
Class - (class of service Y,B,M,etc.)
Booked - No of seats sold

Scenario one seems a little more complex for end users to generate reports
for something like all bookings for departures from cityX to cityY within
specified date ranges.


SCENARIO TWO:

Bookings Table
DepartNo
Origin City
Destination City
DepartDate
Class
Booked

While this seems easier for reporting purposes, multiple other data tables
will also end up being keyed by the combination of DepartNo, Origin,
Destination, DepartDate fields. I am unsure which direction to proceed
since a primary reqisite of the system is to allow users to create their own
reports with available 3rd party reporting tools.
 
Top