advancedTop 30 Scenario-Based Questions

You need to store product specifications that vary by category (laptops have RAM/CPU; clothes have size/color). What's the best database design?

Option 1 (JSONB — recommended for PostgreSQL): ALTER TABLE products ADD COLUMN specs JSONB. Store laptop: {ram_gb:16, cpu:'i7', storage_tb:1}. Store clothes: {size:'XL', color:'red', material:'cotton'}. Create GIN index: CREATE INDEX ON products USING GIN(specs). Query: WHERE specs @> '{"ram_gb": 16}'. Pros: flexible, queryable, indexed. Option 2 (EAV — avoid): product_attributes(product_id, key,

This is a Pro chapter

Sign in, then upgrade to Pro or Power to unlock this and the full Databases Mastery library.

You need to store product specifications that vary by category (laptops have RAM/CPU; clothes have size/color). What's the best database design?

Next Step

Continue to Write a query to calculate a 3-month moving average and year-to-date cumulative total alongside each monthly sales row.← Back to all SQL questions