2

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?

2
  • Well, 'while stocks last' is definitely not a price, so you are right, it would be silly to store these texts as a price. It would be useful to describe the use case a bit more. Until that, my guess is that you have only one of these text per product at any given time - you can just store them in a separate table and add a new column to the pricing that references these. As the pricing usually keeps changing, it's usually a good idea to separate it from the basic product data.
    – dezso
    Oct 30, 2014 at 9:27
  • You are right, I'm confused myself right now haha. While stock last was the time the product is available on the site. But "against any reasonable offer" and just a static price, are different kind of pricing. I'll edit my topic a bit... Oct 30, 2014 at 9:31

1 Answer 1

3

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:

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.

1
  • Looks very good! Also really like the price history part! Thanks, I will discuss this with my colleagues, looks very good. Nov 4, 2014 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.