Normalization & Naming — Series 2

Preview — 3 of 10 questions

A currency column needs to store not just a code, but also a display symbol and a decimal-places count per currency. Which design fits best?

javascript

CREATE TABLE currencies (
  code TEXT PRIMARY KEY,       -- 'USD', 'EUR', ...
  symbol TEXT NOT NULL,        -- '$', '€', ...
  decimal_places SMALLINT NOT NULL DEFAULT 2
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  currency_code TEXT NOT NULL REFERENCES currencies(code),
  amount NUMERIC NOT NULL
);

AA separate currencies lookup table (code PRIMARY KEY, symbol, decimal_places) referenced via foreign key from other tables — this centralizes the currency metadata in one place and lets each currency carry more than just a valid/invalid flag

Bcurrency_code TEXT CHECK (currency_code IN ('USD','EUR','GBP')) repeated on every table that needs it

CA native ENUM type shared across all tables

DStore currency metadata as a hardcoded lookup object in application code only, with no database representation at all

A comments table has commentable_id INT and commentable_type TEXT ('post' or 'photo'), meant to let a comment belong to either a post or a photo. What's the core problem with this design?

javascript

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  commentable_id INT NOT NULL,
  commentable_type TEXT NOT NULL,
  body TEXT NOT NULL
);

APostgreSQL doesn't allow TEXT columns to store type discriminators

Bcommentable_id cannot be backed by a real FOREIGN KEY constraint, since it might point to either posts.id or photos.id depending on commentable_type — referential integrity for this relationship can't be enforced by the database at all, only in application code

CThis design requires a separate table for every possible commentable_type

Dcommentable_type must be an ENUM, or the table will not accept any inserts

An EAV schema stores product attributes as rows instead of columns: (product_id, attribute_name, attribute_value). What's the main trade-off?

javascript

CREATE TABLE product_attributes (
  product_id INT REFERENCES products(id),
  attribute_name TEXT,
  attribute_value TEXT,
  PRIMARY KEY (product_id, attribute_name)
);

-- Retrieving "color AND size for each product" requires self-joins:
SELECT p.id, color.attribute_value AS color, size.attribute_value AS size
FROM products p
LEFT JOIN product_attributes color ON color.product_id = p.id AND color.attribute_name = 'color'
LEFT JOIN product_attributes size  ON size.product_id  = p.id AND size.attribute_name  = 'size';

AEAV enforces stricter type safety than a normal column-per-attribute schema

BEAV is only usable with NoSQL databases, not PostgreSQL

CEAV makes it maximally flexible to add new attributes without a schema migration, but queries that need multiple attributes together require self-joining the same table repeatedly (once per attribute), and there's no native column-level type checking or indexing per attribute

DEAV eliminates the need for any indexes since all data lives in one generic table

Answer all 10 questions (7 more), see explanations for every answer, and track your score.