Re: Table structure dilemma

>> ... difficulty in determing in the correct way to structure a table
...
holds the price of plane flights which fluctates throughout the year. <<

Events have a duration, so you need to show the start and end of a price by calendar dates.

CREATE TABLE
(airline VARCHAR(12) NOT NULL,
 flight_nbr INTEGER NOT NULL
 start_date DATE NOT NULL,
 end_date DATE, -- null means still open  plane_type CHAR(5) NOT NULL, -- some code?  price DECIMAL(10,2) NOT NULL,
 CHECK(start_date < end_date),
 PRIMARY KEY (airline, flight_nbr, start_date));

>> But What if I want to introduce a second type of plane Plane B with
a different set of costs <<

A plane is an (very important) attribute of a flight. Attributes are shown as columns. I'd use the airline and the flight number myself.

You then have a Calendar table which has all the temporal information for your enterprise and you cross join to it for reports. Received on Fri Jan 24 2003 - 15:14:56 CST