1

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?

0

I would have tables for:

  • Supplier

  • Product

  • Inventory

  • Price

  • Currency

  • Sales

  • Country

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.