Building a Flexible Jewellery Filtering System at Scale

Overview

While working on jewellery platform architecture at Nivoda, one of the interesting backend challenges was designing a scalable and flexible filtering system for jewellery products like rings, necklaces, earrings, and bracelets.

The core challenge was:

Instead of hardcoding fields everywhere, we designed a normalized and extensible filtering architecture.

Problem Statement

A jewellery product can contain attributes such as:

The challenge:

We needed: dynamic filtering, admin-driven configuration, scalable search, and efficient querying.

High-Level Architecture

The system was split by category:

jewellery
  |
  ├── jewellery_ring
  ├── jewellery_necklace
  ├── jewellery_earring
  └── jewellery_bracelet

The jewellery table stored common product information. Subtype tables stored category-specific attributes.

Core Database Design

1. Jewellery Table

Stores shared product information across all categories.

CREATE TABLE jewellery (
  id              BIGINT PRIMARY KEY,
  sku             VARCHAR(100),
  category        VARCHAR(50),
  price           DECIMAL(10,2),
  primary_style   VARCHAR(100),
  secondary_style VARCHAR(100),
  created_at      TIMESTAMP
);

2. Ring-Specific Table

CREATE TABLE jewellery_ring (
  id           BIGINT PRIMARY KEY,
  jewellery_id BIGINT,
  ring_size    INT,
  band_width   DECIMAL(5,2)
);

Dynamic Filter System

Instead of adding every filter as a new database column, we introduced a generic filtering engine.

3. Filter Names

CREATE TABLE jewellery_filter_name (
  id   BIGINT PRIMARY KEY,
  name VARCHAR(100)
);

Example rows:

4. Filter Values

CREATE TABLE jewellery_filter_value (
  id             BIGINT PRIMARY KEY,
  filter_name_id BIGINT,
  value          VARCHAR(100)
);

Each value row links back to its parent filter name via filter_name_id:

So Gold belongs to Metal Quality (id 1), and 18K belongs to Metal Carat (id 2).

Product-to-Filter Mapping

5. Ring Filter Values

CREATE TABLE jewellery_ring_filter_value (
  ring_id         BIGINT,
  filter_value_id BIGINT
);

Ring 101 is linked to four filter values — including Metal Quality (Gold) and Metal Carat (18K):

A single ring (id: 101) can now dynamically carry:

Why This Design Worked Well

No Schema Migration for New Filters

Adding a new filter requires no backend deployment and no schema rewrite. Filters like Vintage Collection, Side Stone Type, or Setting Height are just new rows in jewellery_filter_name — and their values are rows in jewellery_filter_value. The system extends naturally as the business evolves.

Flexible Multi-Style Support

Jewellery can carry multiple style dimensions simultaneously:

Primary Style  → Halo
Secondary Style → Solitaire

A single style field would have forced us into comma-separated hacks or arbitrary limits. The many-to-many mapping handles it cleanly.

Better Search Experience

Users could filter using combinations like:

Metal Carat = 18K  AND  Primary Style = Halo

This powered faceted search across large catalogue datasets — the kind of filtering shoppers expect on modern ecommerce platforms.

Example Query

SELECT jr.*
FROM jewellery_ring jr
JOIN jewellery_ring_filter_value rfv
  ON jr.id = rfv.ring_id
JOIN jewellery_filter_value fv
  ON rfv.filter_value_id = fv.id
WHERE fv.value IN ('18K', 'Halo');

Scalability Considerations

As catalogue size grows, joins become expensive. The first line of defence is indexing.

CREATE INDEX idx_filter_value
  ON jewellery_ring_filter_value(filter_value_id);

CREATE INDEX idx_ring
  ON jewellery_ring_filter_value(ring_id);

Search Engine Integration

For large-scale filtering, the normalized schema was synced into search engines like Elasticsearch and OpenSearch.

{
"id": 101,
"category": "Ring",
"primary_style": "Halo",
"secondary_style": "Solitaire",
"metal_carat": "18K",
"metal_quality": "Gold"
}

This enabled millisecond filtering, aggregation counts, faceted navigation, and autocomplete — capabilities that a relational JOIN chain simply cannot match at scale.

API Example

GET /rings?primary_style=Halo&metal_carat=18K

[
{
  "id": 101,
  "name": "Diamond Engagement Ring",
  "primary_style": "Halo",
  "secondary_style": "Solitaire"
}
]

Key Learnings

Building ecommerce systems is rarely about just storing products. The real challenge is flexibility, evolving business requirements, search scalability, admin configurability, and clean domain modelling.