Database design for articles with localized prices

Question

I'm designing a database for a shop for having some fun using Postgresql (but the database should not be really important here, right?). I'd like to design for each article in the catalog multiple prices (e.g. divided by country). I was thinking about the best solution from the point of view of performance. I see three options.

Option 1

The classical approach with junction tables, i.e. three tables:

• article
• price
• article_price

Where price is like

CREATE TABLE price (
  id bigserial PRIMARY KEY,
  amount numeric(6,2) NOT NULL
  country  money_country NOT NULL,
  currency money_currency NOT NULL
)

and money_country and money_currency are two enums. One could say that currency and country should be a table. I believe, but maybe I'm wrong, that I can use some constraint to validate money_country and money_currency pairs, e.g. defining an SQL function instead of having additional tables.

Option 2

I define a different approach which will sacrifice some data integrity by using arrays, e.g.:

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices bigint[] 
)

where prices is an array of id of the rows in price to be linked to the row in article.

Option 3

Probably more "wild", I could use types, e.g.

CREATE TYPE my_money AS (
  --something here to define money
)

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices my_money[] 
)

What's the best approach? Am I missing some?

Answer 1

I would have tables for:

• Supplier

• Product

• Inventory

• Price

• Currency

• Sales

• Country

I would follow tradional Entity Relationship Diagram and model. The more you normalise the design the more efficient it will be because it will be doing less IO. Sometimes you can take shortcuts which might save you a bit of time. But eventually it'll catch up with you and you'll wish you did it properly the first time.

A product could have many prices. Could be sold in many countries. Some countries could have multiple currencies. Prices change over time.

If you expect there will be more than 1 country/currency/price etc then design it to handle multiple countries/currencies/prices etc from the beggining.

So when you define a price you'd want to be able to define a price for this product, in this country, in this currency for this time frame.

Yes its possible to combine all of these things into a compound key, but experience has taught me you dont want keys to have any business meaning.

"Performance" is relative. RDBMs' are extremely good at crunching transactional data. With good design this model will scale up to corporate levels. Reporting and analytics might need a rethink.