Database design patterns and anti-patterns

-- ANTI-PATTERN: EAV (Entity-Attribute-Value)
-- Problem: Difficult to query, no type safety, poor performance
CREATE TABLE eav_bad (
  entity_id INT,
  attribute_name VARCHAR(100),
  attribute_value TEXT -- Everything is text!
);

INSERT INTO eav_bad VALUES
(1, 'name', 'Product A'),
(1, 'price', '19.99'),
(1, 'in_stock', 'true');

-- Query is nightmare
SELECT
  MAX(CASE WHEN attribute_name = 'name' THEN attribute_value END) AS name,
  MAX(CASE WHEN attribute_name = 'price' THEN attribute_value END)::DECIMAL AS price,
  MAX(CASE WHEN attribute_name = 'in_stock' THEN attribute_value END)::BOOLEAN AS in_stock
FROM eav_bad
WHERE entity_id = 1
GROUP BY entity_id;

-- SOLUTION: Use JSONB or proper columns
CREATE TABLE products_good (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200),
  price DECIMAL(10,2),
  in_stock BOOLEAN,
  extra_attributes JSONB -- For truly dynamic attributes
);

INSERT INTO products_good VALUES
(1, 'Product A', 19.99, true, '{"color": "red", "size": "L"}');

SELECT
  name,
  price,
  in_stock,
  extra_attributes->>'color' AS color
FROM products_good
WHERE id = 1;

-- ANTI-PATTERN: Polymorphic associations without constraints
CREATE TABLE comments_bad (
  id SERIAL PRIMARY KEY,
  commentable_type VARCHAR(50), -- 'Post', 'Photo', etc.
  commentable_id INT,           -- Foreign key to what?
  content TEXT
);

-- No referential integrity!
INSERT INTO comments_bad VALUES
(1, 'Post', 999, 'Nice post!'); -- Post 999 doesn't exist

-- SOLUTION: Separate foreign keys or exclusive arc
CREATE TABLE comments_good (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),
  photo_id INT REFERENCES photos(id),
  content TEXT,
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL AND photo_id IS NOT NULL)
  )
);

-- Or: Separate tables
CREATE TABLE post_comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  content TEXT
);

CREATE TABLE photo_comments (
  id SERIAL PRIMARY KEY,
  photo_id INT NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
  content TEXT
);

-- ANTI-PATTERN: God tables (too many columns)
CREATE TABLE users_bad (
  id SERIAL PRIMARY KEY,
  -- 100+ columns here...
  username VARCHAR(100),
  email VARCHAR(100),
  -- Billing info
  billing_address TEXT,
  billing_city VARCHAR(100),
  -- Shipping info
  shipping_address TEXT,
  shipping_city VARCHAR(100),
  -- Preferences
  theme VARCHAR(50),
  language VARCHAR(10),
  -- ...50 more columns
  created_at TIMESTAMP
);

-- SOLUTION: Normalize into related tables
CREATE TABLE users_good (
  id SERIAL PRIMARY KEY,
  username VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP
);

CREATE TABLE user_addresses (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users_good(id),
  address_type VARCHAR(20), -- 'billing', 'shipping'
  street TEXT,
  city VARCHAR(100),
  country VARCHAR(100)
);

CREATE TABLE user_preferences (
  user_id INT PRIMARY KEY REFERENCES users_good(id),
  theme VARCHAR(50),
  language VARCHAR(10),
  timezone VARCHAR(50)
);

-- ANTI-PATTERN: Storing arrays/lists as comma-separated strings
CREATE TABLE posts_bad (
  id SERIAL PRIMARY KEY,
  tags VARCHAR(500) -- 'javascript,react,nodejs'
);

-- Hard to query
SELECT * FROM posts_bad WHERE tags LIKE '%react%';
-- Matches 'react' and 'preact'!

-- SOLUTION: Many-to-many table or array type
CREATE TABLE posts_good (
  id SERIAL PRIMARY KEY,
  tags TEXT[] -- PostgreSQL array
);

INSERT INTO posts_good VALUES
(1, ARRAY['javascript', 'react', 'nodejs']);

-- Query with array operators
SELECT * FROM posts_good WHERE 'react' = ANY(tags);

-- Or proper many-to-many
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id),
  tag_id INT REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

-- ANTI-PATTERN: Improper NULL handling
CREATE TABLE orders_bad (
  id SERIAL PRIMARY KEY,
  total DECIMAL(10,2),
  discount DECIMAL(10,2) -- NULL means no discount, but...
);

-- Bug: NULL in calculation
SELECT total - discount AS final_total FROM orders_bad;
-- Returns NULL if discount is NULL!

-- SOLUTION: Use COALESCE or DEFAULT
SELECT total - COALESCE(discount, 0) AS final_total FROM orders_bad;

-- Or better: Use NOT NULL with DEFAULT
CREATE TABLE orders_good (
  id SERIAL PRIMARY KEY,
  total DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL DEFAULT 0
);

-- ANTI-PATTERN: Duplicate data instead of normalization
CREATE TABLE order_items_bad (
  id SERIAL PRIMARY KEY,
  order_id INT,
  product_id INT,
  product_name VARCHAR(200), -- Duplicated!
  product_price DECIMAL(10,2), -- Duplicated!
  quantity INT
);

-- What if product name/price changes?

-- SOLUTION: Normalize or snapshot intentionally
CREATE TABLE order_items_good (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  -- Snapshot at order time (intentional denormalization)
  price_at_purchase DECIMAL(10,2) NOT NULL,
  name_at_purchase VARCHAR(200) NOT NULL,
  quantity INT NOT NULL
);

-- ANTI-PATTERN: Magic values instead of foreign keys
CREATE TABLE users_bad2 (
  id SERIAL PRIMARY KEY,
  status INT -- 1=active, 2=suspended, 3=deleted
);

-- SOLUTION: Lookup table or enum
CREATE TYPE user_status AS ENUM ('active', 'suspended', 'deleted');

CREATE TABLE users_good2 (
  id SERIAL PRIMARY KEY,
  status user_status DEFAULT 'active'
);

-- Or lookup table for complex cases
CREATE TABLE statuses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE,
  description TEXT,
  display_color VARCHAR(7)
);

CREATE TABLE users_good3 (
  id SERIAL PRIMARY KEY,
  status_id INT REFERENCES statuses(id)
);