Product Reviews and Ratings System for E-Commerce

Developing Product Reviews and Ratings System for E-commerce

Reviews — social proof influencing conversion more than product description. Average rating and review count display in Google snippets via structured data, giving search advantage. Takes 4–7 business days.

Data Model

CREATE TABLE reviews (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    order_item_id BIGINT REFERENCES order_items(id),
    user_id BIGINT REFERENCES users(id),
    guest_name VARCHAR(100),
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(255),
    body TEXT,
    pros TEXT,
    cons TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE review_photos (
    id BIGSERIAL PRIMARY KEY,
    review_id BIGINT REFERENCES reviews(id) ON DELETE CASCADE,
    url VARCHAR(500) NOT NULL,
    sort_order SMALLINT DEFAULT 0
);

CREATE TABLE review_votes (
    review_id BIGINT REFERENCES reviews(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    vote BOOLEAN NOT NULL,
    PRIMARY KEY (review_id, user_id)
);

Who Can Review

Three access models:

Recommended: authenticated users + "Verified purchase" badge for those with completed order with product.

Moderation

Can be manual or automated. Auto rules:

• Verified buyer, 4–5 rating, no stopwords → auto approved
• First-time user review → pending check
• Text contains URLs, phone, stopwords → pending or spam

Rating Recalculation

public function recalculateRating(): void {
    $stats = $this->reviews()
        ->where('status', 'approved')
        ->selectRaw('COUNT(*) as count, AVG(rating) as avg')
        ->first();

    $this->update([
        'rating_avg'   => round($stats->avg, 2),
        'rating_count' => $stats->count,
    ]);

    Cache::forget("product:{$this->id}:rating");
}

Rating stored denormalized in products for fast sorting.

Rating Distribution

Show not just average but histogram:

const RatingDistribution = ({ distribution }: { distribution: Record<number, number> }) => {
  const total = Object.values(distribution).reduce((a, b) => a + b, 0);

  return (
    <div className="space-y-1">
      {[5, 4, 3, 2, 1].map(star => (
        <div key={star} className="flex items-center gap-2">
          <span className="w-4 text-sm">{star}</span>
          <div className="flex-1 h-2 bg-gray-100 rounded-full overflow-hidden">
            <div
              className="h-full bg-yellow-400"
              style={{ width: `${((distribution[star] ?? 0) / total) * 100}%` }}
            />
          </div>
          <span className="text-sm text-gray-500 w-8">{distribution[star] ?? 0}</span>
        </div>
      ))}
    </div>
  );
};

Store Replies

Manager can reply from admin. Response displays under review with "Store reply" label.

Structured Data for SEO

Reviews exported to JSON-LD for Google:

const ProductSchema = ({ product }: { product: ProductDetail }) => (
  <script type="application/ld+json">{JSON.stringify({
    '@context': 'https://schema.org',
    '@type': 'Product',
    name: product.name,
    aggregateRating: {
      '@type': 'AggregateRating',
      ratingValue: product.rating_avg,
      reviewCount: product.rating_count,
      bestRating: 5,
      worstRating: 1,
    },
  })}</script>
);

Stars appear in snippets with 1+ review.

Sorting and Filtering

Reviews sorted: by date, rating (high/low), helpfulness. Star count filter — clickable histogram rows. Helps buyer find relevant reviews, reduces bounce.