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:
The product per se, with all its (hardly changing) attributes. Goes into table product
.
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.