CODEWITHSUNDEEP
sqldatabasedatabase-design
Jim
Jim

Reputation: 4669

What is the recommended database schema for overlapping rules (such as price changes)?

Envision the rules that govern the price of a hotel room.

  1. In general, $100 a night
  2. On Fridays or Saturdays, $120
  3. In the summer months, $150
  4. For a special next week, $80
  5. Etc..

Given a database of hotel rooms with varying rules like this, how would you model this in the database so that you can quickly and easily modify and query the price at a given time?

Upvotes: 0

Views: 171

Answers (2)

jcaron
jcaron

Reputation: 17710

You need to define an order of priority. Then you store each rule with its priority and its criteria (from - to + weekdays bitmap for instance), and you find the matching rule with the highest priority.

Upvotes: 1

Moon_Watcher
Moon_Watcher

Reputation: 468

I guess there's multiple ways you could do that, but the one that I'm most familiar with is to store attributes 'date-from' and 'date-to' in the table along with the corresponding price for that duration. Then, while querying you could specify sysdate(or any other desired date) in the where clause to retrieve the correct price.

Alternatively, if you had the same rules for all rooms in the hotel, you could create a separate table with the rules(date-from, date-to, price(or %change in price)). This would be a more normalized way of doing it, but that would mean you have the same rules for all rooms.

It all depends on what the business rules are, really.

Upvotes: 1

Related Questions