I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). There are also two different types of prices (product and delivery price). Now I am having trouble with this. I made it to work, but I somehow feel bad about it when I write queries (they tend to get long and complicated).
I designed two tables like this (they are same):
base_prices
| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |
delivery_prices
| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |
For a user specific price referent id would be a positive integer, matching users id. For a group specific price referent id would be a negative integer, matching group id (I made group ids negative integers). For global product price, referent id will be NULL. This way, when I query price for a specific item and specific user and his group all I need to do is filter table by item_id and order results by referent_id column in descending order. This way, user specific are on top, group after and global are last.
Date ranges valid_from-valid_until cannot overlap for two rows.
Now a query to get current prices would look something like this:
SELECT
*
FROM
base_prices AS outer
WHERE
id = (
SELECT
id
FROM
base_prices AS inner
WHERE
NOW() BETWEEN valid_from AND valid_until
AND inner.item_id = outer.item_id
AND (
inner.referent_id = # HERE GOES USER ID
OR inner.referent_id = # HERE GOES GROUP ID
OR inner.referent_id IS NULL
)
ORDER BY
inner.referent_id DESC # FOR SPECIFICITY
Thing that bugs me is that I always have to write this kind of sub-query to get items, and this doesn't seem as a good design to me. When data starts to build up, this queries will cost much more. Also, I had to write a few triggers to keep data integrity during inserts and updates.
My opinion is that current prices and historical prices need to be separated, but I was in a hurry, so this is what came up. I was thinking about scheduling events during inserts based on valid_from column that will move data from historical prices table to (new) current prices table.
I am inexperienced, so this seems kind of weird to me. It might be OK, though, but I would like to see some suggestions and opinions.
If I left out something or there is not enough info, leave a comment.