CODEWITHSUNDEEP
phpmysqldatetime
Layke
Layke

Reputation: 53156

Seasonal Pricing for an Entity.

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.

Upvotes: 3

Views: 511

Answers (2)

Thomas
Thomas

Reputation: 64645

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

Upvotes: 1

LeonT
LeonT

Reputation: 56

Start with a season table that defines the date ranges for the seasons. It should have a primary key field, say season_id. Then have another table to store room, price and season_id. The season_id is a foreign key to the season table.

Upvotes: 2

Related Questions