Design a Review and Rating System

PostgreSQL: Reviews

CREATE TABLE reviews (
    review_id       UUID PRIMARY KEY,
    product_id      VARCHAR(50) NOT NULL,
    user_id         UUID NOT NULL,
    order_id        UUID,
    rating          SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title           VARCHAR(200),
    body            TEXT,
    photos          JSONB,
    verified_purchase BOOLEAN DEFAULT FALSE,
    helpful_count   INT DEFAULT 0,
    unhelpful_count INT DEFAULT 0,
    status          ENUM('published','suppressed','pending_review','deleted') DEFAULT 'published',
    fraud_score     DECIMAL(4,3),
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ,
    UNIQUE (product_id, user_id),  -- one review per user per product
    INDEX idx_product (product_id, status, created_at DESC),
    INDEX idx_product_helpful (product_id, status, helpful_count DESC),
    INDEX idx_user (user_id, created_at DESC)
);

CREATE TABLE product_ratings (
    product_id      VARCHAR(50) PRIMARY KEY,
    avg_rating      DECIMAL(3,2),
    total_reviews   INT DEFAULT 0,
    star_1 INT DEFAULT 0, star_2 INT DEFAULT 0, star_3 INT DEFAULT 0,
    star_4 INT DEFAULT 0, star_5 INT DEFAULT 0,
    updated_at      TIMESTAMPTZ
);

CREATE TABLE review_votes (
    user_id         UUID NOT NULL,
    review_id       UUID NOT NULL,
    helpful         BOOLEAN NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, review_id)
);

CREATE TABLE seller_responses (
    response_id     UUID PRIMARY KEY,
    review_id       UUID NOT NULL UNIQUE,
    seller_id       UUID NOT NULL,
    body            TEXT NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

Redis

# Aggregate ratings cache
rating:{product_id}            --> Hash { avg, total, s1, s2, s3, s4, s5 }
TTL: 300

# Helpful vote dedup
voted:{user_id}:{review_id}    --> "1"
TTL: 86400

# Review count per user per day (rate limit)
review_rate:{user_id}:{date}   --> INT
TTL: 86400

Elasticsearch: Review Search

{
  "review_id": "rev-uuid", "product_id": "prod-123",
  "rating": 4, "title": "Great quality",
  "body": "The product quality is excellent but shipping was slow...",
  "verified": true, "helpful_count": 234, "created_at": "2026-03-10"
}
// Queries: "battery life" within reviews of product X
// Filter by: rating >= 4, verified only, sort by helpful