Arrays for Product Tags (No Junction Table)

intermediate

E-commerce site needs to store product tags. Traditional approach: Junction table (products_tags) with JOIN. Problem: Slow queries (JOIN overhead). Solution: Use arrays to store tags directly (no JOIN needed).

-- Problem: Junction table approach

CREATE TABLE products_traditional (

product_id SERIAL PRIMARY KEY,

name VARCHAR(200)

);

CREATE TABLE tags (

tag_id SERIAL PRIMARY KEY,

tag_name VARCHAR(50)

);

CREATE TABLE products_tags (

product_id INTEGER REFERENCES products_traditional(product_id),

tag_id INTEGER REFERENCES tags(tag_id),

PRIMARY KEY (product_id, tag_id)

);

-- Query requires JOIN (slow)

SELECT p.product_id, p.name, array_agg(t.tag_name) as tags

FROM products_traditional p

JOIN products_tags pt ON p.product_id = pt.product_id

JOIN tags t ON pt.tag_id = t.tag_id

WHERE t.tag_name = 'electronics'

GROUP BY p.product_id, p.name;

-- Requires 2 JOINs, slower

-- Solution: Array approach

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

name VARCHAR(200),

tags TEXT[] -- Array of tags

);

-- Create GIN index for fast queries

CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Insert with array

INSERT INTO products (name, tags)

VALUES

('Laptop', ARRAY['electronics', 'computers', 'portable']),

('Phone', ARRAY['electronics', 'mobile', 'smartphone']),

('Desk', ARRAY['furniture', 'office']);

-- Query: Find products with specific tag (no JOIN)