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?