database design - keep dublicate data in seperate tables

[astromenix]

Hello everyone
My situation is that I m workin on this project that is about a travel management system. Now at the administration part, we have set up the tables n screens to collect the data for the types of rooms, periods, their discounts, and the rates that will b bought n sold.
One of the requirements is that the hotel owner will be able to change the periods and prices acordingly. Our client asked to dublicate all the date in new tables where the changes will take place and keep the initial tables inact as the hotel contract data. But one room can have up to 10 rates and growin the number of hotels and period for each of the room, plus the discounts will have to keep a big number of records, which doesn’t seem to be good for our db size. Any help and thoughts will be greatly apreciated.
Thanx
astromenix

 

[Madawc]

My thought would be three tables - one for bookings, one for rooms and one for rate types.

Madawc Williams (East Anglia, UK)

 

[SimonSellick]

Hi,

Why do the rates change? There is presumably some good business reason. Perhaps different types of customer get different rates, or the rates change when special promotions or deals are offered, or just by the day of the week, the time of the year, etc? Or some combination of these?

One approach would be to give each set of rates a promotion code and use this code as part of the key in the rates table (the rest of the key would be the room type or number and perhaps the occupancy). So you might end up with a promotion code for weekdays, another for weekends, another for long weekends, another for peak periods in teh year...and so, on.

In general, if the rate charged depends on several factors, then each of these factors should form part of the key to the rates table. If there are also occasional 'overall' price changes, then you could use start- and end-validity dates in the rates table to determine which price to use.

HTH

 

[astromenix]

Hi again and thanks a lot for your replies.
TNow the rates have two characteristics (keys) one taken of the physical room (each hotel has different types of physical rooms) and another key taken of a period. Now the same room also can have different rates.
For example
A 3 bed room (occupancy) can be sold for one, two or three persons which results in 3 different rates plus one corporate rate.

Now the hotel owners can modify the prices for a specific room rate by defining a different period, which may or not, ovelap one or more periods existing periods.

As for the day of the week is concidered a discount that will be deducted of the rates price.

SimonSellick your proposal sounds good to me although i may be wrong.
I should use the same periods and rates tables to store the new rates and distingusih between them by using a flag.
Then when i will perform a serach i d have to go n search for all the rates that satisfly may query and choose which one to use (old contract rarates or “updated”/”newly” rates.

thanks