Hotel rooms prices: how to manage discounts that changes according to some conditions

I'm writing an app to manage rooms in a Hotel and I've to design the DB to manage discounted price. I've already made the Room and Price tables and now I've to create some tables where I can store discounts that the admins could apply according to some conditions. The admins could create new discounts too. For example:

An user could get 10% discount if the reservation starts in September.
An user could get 10% discount if he is going to book the room during 2 weeks

So, here are my simplified tables (I omitted fields and relations that are not related to the question):

Rooms:

id | name
---|----------------
 1 | Trafalgar Square
 2 | Piccadilly

Prices:

id | room_id  | base | promotion 
---|----------|------|-----------
 1 |    1     | 10   | 8
 2 |    2     | 25   | 20

How can I store conditions as the ones stated above? I'm using Rails and the first solution I thought about was to create a table where I can store the discount is going to be applied and the condition to apply it. I thought the field condition as a SQL snippet; something like Reservation.start_date > [date], thus I could apply more discounts concatenating them in Rails. But this is a very ugly solution and is coupled with the DBRMS, so I'd like to avoid this choice.

Do you know how can I implement something to solve this problem? I hope to have explained the problem clearly.

Solution

Here's an expanded version of my comment. I agree that putting in SQL/executable code in the db is generally a bad idea, and I think you can avoid that in this case.

I would do it this way:

promotion
------------------------------------------------------------
id (pk) | shortname (uniq) | params?   |  serialised_params?
1       | september_promo  | ...       |
2       | long_stay        | ...       |
------------------------------------------------------------

room_promotion
----------------------------------------------------
room_id (fk) | promotion_id (fk) | serialised_params
1            | 1                 | { discount=10; start_date=x; end_date=y }
2            | 2                 | { length=14 }
----------------------------------------------------

This approach gives you maximum flexibility with the kinds of promotion you might want to run. Since each promotion has a number of parameters depending on the business logic, I've suggested that in the per-room case you have a column of serialised parameters, so you don't have a large number of unused columns in this table. Do bear in mind that this does break a few relational rules, but used sparingly and with an awareness of its limitations, it is a useful approach.

You could equally have parameters or serialised parameters against each promotion row too (say if you knew that all uses of the 'long_stay' promo would be for 14 days).

Note I've arranged the 'room_promotion' table so that more than one promotion could be applied to a room. You could make (room_id, promotion_id) a primary key if you're sure that one will always only be applicable at one time.

OK, so now you have a mechanism by which 'promotion rules' can be applied. But, the business logic still belongs in your code. So, look up the shortname, camel-case it, and then use a dynamic look-up mechanism in Ruby to apply the discount. I don't use Ruby, but in PHP sketch-form it might look like this:

    Get object $Bill (prior to discount being applied)
    $Discount = DiscountFactory::getInstance(
        'september_promo',
        unserialize($serialised)
    );
    $Bill->applyDiscount($Discount);
    Display modified bill

Internally, I reckon september_promo gets converted to SeptemberPromo, which is a class thus (sorry, PHP again):

class SeptemberPromo extends Promotion
{
    // Add in concrete implementation of abstract method here
    public function executeDiscount($Bill) { ... }
}

In turn, Promotion can have an abstract method that is used by applyDiscount() to determine what discount to apply, given the per-room values, the per-promo values, and the details of the bill.

So long as you keep your discount business logic fairly generic, your hotel managers will be able to customise it using the serialised parameters, rather than needing new Ruby code for every fresh promotion (unless they want a promotion type that is new). With that in mind, SeptemberPromo is perhaps too specific - maybe MonthSpecial?

Obviously you'll need to build a UI to let a hotel manager store new promotion and room_promotion rows, but that should be pretty easy.