New Table vs Adding Columns To Existing Table

Question

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?

• Jon of All Trades: Here is a similar question, to which the conclusion is "add columns," because the cardinality is very low. In your case, Denny, the cardinality is high, or at least non-trivial.

Answer 1

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?

Answer 2

The principles of normalization say: make a new table. If you add new columns to your existing table, you'll need to make schema changes (ADD COLUMN Price_Italy) when your data changes. A good recipe for job security, but not for scalability.

A third option would be to add CountryID to the PK for Products. You could have product names, shipping rates, available colors, etc., vary by country, in addition to prices.

Answer 3

Just in case it helps anyone.

You also have an option for Postgres JSONB column type if all that is required is single column (country prices) as per OP.