Postgresql – New Table vs Adding Columns To Existing Table

database-designpostgresql

I have a table, products with the following columns: id, name
There is another table, countries with these columns: id, name, code
The products have different prices across different countries (same currency is used).

I'm torn between creating a new table to store this, which would have the following columns: product, country, price
and adding new colums to the products table; price_uk, price_kenya, price_sweden, etc, for the different countries.
Which would be the better option, and why?

Best Answer

Instead of arguing normalization rules, I will use predicates and constraints.

[P1] Product number (PRODUCT_ID) named (PRODUCT_NAME) exists.

(c1.1) Product is identified by product number.

(c1.2) For each product that product has exactly one product name; for each product name exactly one product has that product name.

product {PRODUCT_ID, PRODUCT_NAME}  -- P1
    KEY {PRODUCT_ID}                -- c1.1
    KEY {PRODUCT_NAME}              -- c1.2

[P2] Country named (COUNTRY_NAME) with assigned country code (COUNTRY_CODE) and number (COUNTRY_ID) exists.

(c2.1) Country is identified by country number.

(c2.2) For each country, that country is assigned exactly one country name; for each country name, that country name is assigned to exactly one country.

(c2.3) For each country, that country is assigned exactly one country code; for each country code, that country name is assigned to exactly one country.

country {COUNTRY_ID, COUNTRY_NAME, COUNTRY_CODE}  -- P2
    KEY {COUNTRY_ID}                              -- c2.1
    KEY {COUNTRY_NAME}                            -- c2.2
    KEY {COUNTRY_CODE}                            -- c2.3

[P3] Product (PRODUCT_ID) in country (COUNTRY_ID) is assigned price of (AMOUNT) (CURRENCY).

(c3.1) For each country and product, that combination of that country and that product occurs at most once.

(c3.2) For each combination of country and product, that product in that country has exactly one price.

(c3.3) For each price, it is possible that more than one product in a country has that price.

(c3.4) If a product is assigned a price for a country, then that product must exist.

(c3.5) If a product is assigned a price for a country, then that country must exist. 

country_price {COUNTRY_ID, PRODUCT_ID, AMOUNT, CURRENCY}  -- P3
          KEY {COUNTRY_ID, PRODUCT_ID}                    -- c3.1

          FK1 {PRODUCT_ID} REFERENCES product {PRODUCT_ID} -- c3.4
          FK2 {COUNTRY_ID} REFERENCES country {COUNTRY_ID} -- c3.5

Note:

[Px]   = predicate  x
(cx.y) = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

So this is basics, as per your first suggestion.

Note that adding pricing for a new country does not involve schema changes, but adding data. What happens to the application code if you were to add a column to the product table instead?

Consider the following questions:

  • For which countries do we have all product prices defined?

  • For which countries we do not have any product pricing defined?

  • Which countries do not have pricing for all products defined -- but do have at least one?

  • Which product prices are not defined in country X?

These are straightforward queries. An analyst may prepare these queries and the code will not change as you keep adding new countries and products. Now try to formulate these queries for a model where you add columns to the product table. What happens to the query code if you were to add a column to the product table?

Related Question