Calculating Cascading Price Rules in SQL

Question

I am building a tool that allows people to post items into categories. Before the item is posted, a price calculation needs to take place to determine the price to charge the user who's posting the item.

I have come up with the concept of using a table called price_rule to define the rules and it would look something like this:

+------------ +
| Field       |
--------------+
| id          |
| user        |
| category    |
| price       |
+-------------+

As a general rule, this database will always have a fallback row. This row is used to determine the price in the event that no other rules match the specific context. That row would like this:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+

With this row in place and no other rows, a post will always cost $10.00.

Now suppose an additional row is added. The table now looks like this:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+
| 2         | Bob     | NULL        | 8.00  |
+-----------+---------+-------------+-------+

With the addition of this rule, Bob will pay $8.00 per posting in all categories, and the rest of the users will pay $10.00 in all categories.

If we add more rows that include specific users and specific categories:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+
| 2         | Bob     | NULL        | 8.00  |
+-----------+---------+-------------+-------+
| 3         | Bob     | Bicycles    | 9.50  |
+-----------+---------+-------------+-------+
| 4         | Meghan  | Bicycles    | 5.00  |
+-----------+---------+-------------+-------+

When Bob goes to make a post in the bicycles category, his price would be $9.50. Any other category, and Bob will pay $8.00.

Meghan will now pay $5.00 for a posting in the bicycles category, and $10.00 for every other category.

Every other user posting in any category (including Bicycles) would pay the default price of $10.00.

In the real world, this table could potentially have several hundreds of rows, which would allow the price for a posting to be finely controlled.

In case you're wondering, there are business motivations behind this concept as the cost to make posts in this system is not always deterministic. It is instead based on the business relationship with the user making the post.

The problem reveals itself when trying to design a query that returns the single most relevant pricing rule that applies to a post being made. When querying this table, I would have access to the following information: user and category. I have tried a combination of queries and have read about a number of SQL concepts such as IFNULL and COALESCE but I haven't been able to nail the right query.

Another concern is that in our real application there is an additional column in price_rule table to base the prices off, but I have left this detail out to make the example used in this question simpler. I feel that the same solution would likely apply whether there are 2 columns or 3 columns used for the calculation.

Please note that there are constraints enforced in the app code that prevent duplicate rules from being added.

We are also using Doctrine ORM and Doctrine DBAL, so if your query works out of the box with the Query Builder or DQL, your answer will be considered more valuable. Solutions in standard SQL workable in either PostgreSQL or MySQL are also acceptable.

Although I would like to avoid this as much as possible, a valid solution may also include fetching every row from the price_rule table and determining the applicable rule using app code. If your solution is based around this concept, please include relevant pseudocode.

Answer 1

Postgresql Fiddle

create or replace function price_rule(
    _user varchar(50), _category varchar(50)
) returns setof price_rule as $$

select id, "user", category, price
from (
    select *, 0 as priority
    from price_rule
    where category = _category and "user" = _user

    union

    select *, 1 as priority
    from price_rule
    where "user" = _user and category is null

    union

    select *, 2 as priority
    from price_rule
    where "user" is null and category is null
) s
order by priority
limit 1
;
$$ language sql;

I turned the above query into a function just to make it easy to test. But just unwrap it if you prefer.

In MySQL I don't remember how to make a function so it is raw:

MySQL Fiddle

select id, `user`, category, price
from (
    select *, 0 as priority
    from price_rule
    where category = 'Bicycles' and `user` = 'Bob'

    union

    select *, 1 as priority
    from price_rule
    where `user` = 'Bob' and category is null

    union

    select *, 2 as priority
    from price_rule
    where `user` is null and category is null
) s
order by priority
limit 1;

Answer 2

A simple way would be to score each row by fit, and pick the row with the highest score, to match Bob/Bicycles something like;

SELECT price,
  CASE WHEN "user" = 'Bob' THEN 2 
       WHEN "user" IS NULL THEN 0
       ELSE -10 END +
  CASE WHEN "category" = 'Bicycles' THEN 1
       WHEN "category" IS NULL THEN 0
       ELSE -10 END score
  FROM field
ORDER BY score DESC LIMIT 1;

This gives 2 points for a name match, and 1 point for a category match. Any mismatch gives -10 which allows the default to win if nothing else matches well.

An SQLfiddle to test with.

If you have a great number of rows, you'll want to add a WHERE clause which finds only rows that match (ie matching name/category or null) and just use the order by on the filtered rows.