Ad

Seasonal Pricing For An Entity.

- 1 answer

I am trying to manage seasonal prices for hotel rooms.

The only way that I can think of doing it would be to use:

A = Room Rate

B = Service Charge for room

Imagine that the table has a roomId column which is omited from below.

| DayDate   |EndDate   |  A  |  B
-----------------------------------------------
| 2010/07/1 |2010/07/2 | 200 |  40
| 2010/07/3 |2010/07/4 | 150 |  40
| 2010/07/5 |2010/07/5 | 150 |  50
| 2010/07/6 |2010/07/7 | 200 |  50
| 2010/07/8 |2010/07/9 | 100 |  60

etc.. (table taken from another question).

The problem is: I don't want my seasons to be year specific. Seasons for rooms shouldn't change year on year. I don't want my users to have to enter the seasonal information several times.

I am also going to have thousands of rooms, so I don't know a way to make this easily manageable.

I'm using mysql and php.

Ad

Answer

Create Table Prices
    (
    MonthStart int not null
    , DayStart int not null
    , MonthEnd int not null
    , DayEnd int not null
    , A int  not null
    , B int not null
    )

Insert Prices( MonthStart, DayStart, MonthEnd, DayEnd, A, B )
Select 7, 1, 7, 2, 200, 40
Union All Select 7, 3, 7, 4, 150, 40
Union All Select 7, 5, 7, 5, 150, 50
Union All Select 7, 6, 7, 7, 200, 50
Union All Select 7, 8, 7, 9, 100, 60

It should be noted that this approach presumes that the boundaries of the seasons are specific to the month and day regardless of year or circumstance. In addition, you'll have to decide how to handle leap year. Another approach which might be simpler is to simply enumerate every day of the year:

Create Table Prices
    (
    MonthStart int not null
    , DayStart int not null
    , A int  not null
    , B int not null
    , Constraint PK_Prices Primary Key ( MonthStart, DayStart )
    )

Insert Prices( MonthStart, DayStart, A, B )
Select 7, 1, 200, 40
Union All Select 7, 2, 200, 40
Union All Select 7, 3, 150, 40
Union All Select 7, 4, 150, 40
Union All Select 7, 5, 150, 50
Union All Select 7, 6, 200, 50
Union All Select 7, 7, 200, 50
Union All Select 7, 8, 100, 60
Union All Select 7, 9, 100, 60
Ad
source: stackoverflow.com
Ad