Re: Table structure dilemma

>>>>> "chris" == chris <xnuus_at_hotmail.com> writes:

 chris> Hi
 chris>    Okay Im having a little difficulty in determing in the
 chris>    correct way
 chris> to structure a table in my database for a problem im working
 chris> on. It holds holds the price of plane flights which fluctates
 chris> throughout the year. For instance:

 chris> week 1 = $100 week 2 = $120 week 3 = $132 week 4 = $70 . .  chris> week 52 = $115

 chris> Okay so I got a table: with the column headings Week and Price

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

 chris> Plane B

 chris> week 1 = $200 week 2 = $320 week 3 = $232 week 4 = $170 . .  chris> week 52 = $215  

 chris> what do I do decide to have as the table headings ?

 chris> option 1 - have the column headings as Week, Plane_type ,  chris> Price

 chris> or

 chris> option 2 - have a column headings Plane type, week1_price,  chris> week2_price ... week52_price

 chris> option 1 seems right but I would be surely minimising the size
 chris> of the table with option 2 because I would not need to repeat
 chris> the week number.

 chris> Thanks

 chris> Chris

I guess the 1st option is better, but I'd be tempted to go another way and create more tables e.g.

create table rates (
rate_id,
amount
)

create table planes (
plane_id,
... (attributes associated with a plane) )

create table flights (
flight_id,
plane_id,
... (other details associated with a flight )

create table tarriff (
flight_id,
valid_from_date,
valid_to_date,
rate_id
)

You could remove the valid_to_date and assume it is valid if there is no other flight with the same flight_id with a later valid_from_date. The *_id are not necessarily numeric or surrogate keys. Having the amount table reduces repetition of amount values etc. I'm also assuming there is a 1-to-1 relationship between flights and planes.

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!