Different kinds of pricing PostgreSQL

Question

I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this:

+----+------+-----------+-----------+
| ID | Name | price_old | price_new |
+----+------+-----------+-----------+
| 1  | ball |   12.00   |   8.00    |
+----+------+-----------+-----------+

EDIT:

Now we would like to handle different kinds of pricing. Not only decimals (like in price_old and price_new), but also like "against any reasonable offer". There is only one option, static price OR some kind of text. What do you think what's best? Separate table for pricing, or extra columns. I think it's not so smart to change price_old and price_new to varchar instead of decimal, so more kinds of pricing are available in one column... Cause it can cause trouble I guess.

Further we have different scenarios of availability of products on our site. A product can be available for 24 hours, or while stocks last. Guess it would be a good idea to have a separate table for this also? I always think it's not a good idea to have different columns to choose from in one table, like if one column is empty use the other one. Not that flexible... But what do you guys think?

Answer 1

According to the true nature of things (as I understand it), I suggest two tables:

CREATE TABLE product (
   product_id serial PRIMARY KEY
 , product    text NOT NULL
 --   more attributes of the product
);

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price      int           -- prices in cent
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

We are dealing with two different entities:

1. The product per se, with all its (hardly changing) attributes. Goes into table product.

2. The deal you are offering, with a price, a "price alternative" (price_alt) a start time (valid_from), an optional end time (valid_to), ... Table offer.

This adequate model has technical advantages:

• You automatically have a history of prices, which you can keep or delete or backup (and restore without interfering with the working system).

• Most of the data goes into product. Most of the updates go into offer, in the form of new entries, so INSERTs really. INSERTing small rows is much cheaper than UPDATEs on big rows.

You can always add a VIEW (or MATERIALIZED VIEW if you need the read performance) to provide a complete table of current offers including product details.

Price

Assuming freely formulated price alternatives (price_alt), hence data type text. If it's just the same recurring phrases, use an enum or create a lookup-table and only store a price_alt_id in offer.

I would define that price_alt overrules price. There can still be a price (might serve as guideline for haggling or as minimum), but that's optional. The constraint some_kind_of_price_required enforces that some kind of price is always present.

Either way, I'd expect an actual price in the majority of offers. Use an integer column and store the amount in cent. That's typically simpler and faster. An int only occupies 4 bytes, where a numeric for your case typically occupies 8-12 bytes.

Additional columns with mostly NULL values are cheap:

• Calculating and saving space in PostgreSQL
• Adding nullable column to table costs more than 10 minutes

The design with an additional column for price_alt is much more effective and also much cleaner than switching to text representation of numeric prices (that would be a very bad decision). A view could show a single price column as formatted text (overruled by price_alt).

I would define valid offers as:

• the latest offer per product with valid_from pre-dating "now"
• valid_to IS NULL or greater than "now"
• product in store (outside the scpoe of this answer).

So there is at most one offer per product at any given time.