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.
The classical approach with junction tables, i.e. three tables:
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 )
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_currency pairs, e.g. defining an SQL function instead of having additional tables.
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 )
prices is an array of id of the rows in price to be linked to the row in
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?