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
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
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
MATERIALIZED VIEW if you need the read performance) to provide a complete table of current offers including product details.
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
I would define that
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
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.