JSON Columns for Flexible Product Attributes

intermediate

E-commerce site needs flexible product attributes (different attributes per category). Traditional approach: Multiple columns or EAV pattern (complex). Problem: Schema changes require ALTER TABLE (slow, downtime). Solution: Use JSON columns for flexible attributes.

-- Problem: Fixed schema approach

CREATE TABLE products_traditional (

product_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(200),

-- Laptop attributes

ram VARCHAR(20),

storage VARCHAR(20),

processor VARCHAR(50),

-- Phone attributes (NULL for laptops)

camera VARCHAR(20),

battery VARCHAR(20)

-- Adding new attribute requires ALTER TABLE (slow, downtime)

);

-- Solution: JSON column approach

CREATE TABLE products (

product_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(200),

category VARCHAR(50),

price DECIMAL(10, 2),

attributes JSON -- Flexible attributes

) ENGINE=InnoDB;

-- Insert laptop with laptop-specific attributes

INSERT INTO products (name, category, price, attributes) VALUES

(

'MacBook Pro 16"',

'Laptops',

2499.00,

JSON_OBJECT(

'brand', 'Apple',

'ram', '16GB',

'storage', '512GB SSD',

'processor', 'M3 Pro',

'screen_size', '16 inch'

)

);

-- Insert phone with phone-specific attributes

INSERT INTO products (name, category, price, attributes) VALUES