Build a searchable product catalog step by step: categories, product fields and filters

A catalog stops being searchable at roughly the point where the spreadsheet no longer fits on one screen. The fix is not a nicer page — it is deciding, before you build anything, what a visitor has to be able to answer:

1. Do you have it? (part number, model, alias)
2. Is this the exact variant I need? (size, voltage, material, connector)
3. How does it compare to the thing I am replacing? (attributes side by side)

Every step below serves one of those three. If a field or a filter does not serve one of them, it is decoration.

Step 1 — One row per sellable SKU

A product family is a marketing idea. A SKU is the thing someone can put in a cart or a quote request. Mixing the two is the most common reason filter counts are wrong.

Example: a cable family with 3 lengths × 2 connector types = 6 SKUs, not 1 product with 6 options. If you model it as one product, a filter on length returns one row and a filter on connector returns one row, and the counts on both are meaningless.

Start with one spreadsheet, one row per SKU:

sku,name,brand,category,price,currency,moq,unit,length_mm,connector,package,image,description

Write down the rule you use for SKU codes before you fill 400 rows. Changing the scheme afterwards means re-keying every image filename and every link.

Step 2 — One primary category per product, everything else is an attribute

A product that lives in three categories gets three URLs, contributes to three filter counts, and creates a canonical-URL question you will be answering for months. Most of the time that product does not belong in three categories — it has three attributes (application, material, use case) that someone mistook for categories.

Two levels is enough for most catalogs. Three if the domain genuinely has three (a distributor with department → family → series). Anything deeper is usually a filter wearing a category's clothes.

Step 3 — Split fields into four groups

If a value will appear in a filter, it must come from a fixed list or be a number. The moment a filterable field accepts free text, someone types Aluminum and someone else types aluminium and your facet splits in two.

Step 4 — Normalise before you import

Do this in the spreadsheet, not in code, so a human can review it.

Keep a two-column mapping sheet. When the next import arrives you will run it through the same mapping instead of guessing why the facet count dropped.

Step 5 — Decide which filters earn their place

Six rules that survive contact with real catalogs:

1. A filter must split the current result set. If every product in the category has the same value, hide the filter in that category. Show it where it distinguishes something.
2. Enumerations get checkboxes, numbers get ranges. A checkbox list of 200 part numbers is a search box that misunderstood its job.
3. Above roughly 30 distinct values, switch to a search field or a range. A rule of thumb, not a law — but a scrollable list of 200 checkboxes is not a filter.
4. Show counts next to each option, and make them counts within the other active filters. A count that ignores the other selections is a lie the visitor discovers after clicking.
5. Filter state belongs in the URL. ?material=Aluminum&voltage=24 survives a bookmark, a share, and the back button. A filter held only in memory does not.
6. Keep it to 4–6 visible filters. More than that and people stop reading and start typing in the search box — which is fine, but then the filters are noise.

Step 6 — Search part numbers differently from words

Part numbers need exact and prefix matching. Product names need fuzzy matching. Treat them as two paths and merge the results:

• exact sku match → top of the list, full row
• alias / cross-reference match → top of the list, with a "this is the equivalent of X" label
• prefix match on sku → next
• fuzzy match on name and description → below that

The alias table is what makes the third question answerable: a visitor arrives holding a number that is not yours, and leaves knowing which of yours replaces it — or knowing that none does. A catalog that quietly returns nothing for an unknown part number is worse than one that says no match clearly, because the visitor cannot tell whether you lack the part or lack the search.

Step 7 — A schema that supports faceted filtering

SQLite dialect; the same shape works elsewhere with minor edits.

CREATE TABLE categories (
  id        INTEGER PRIMARY KEY,
  parent_id INTEGER REFERENCES categories(id),
  slug      TEXT UNIQUE NOT NULL,
  name      TEXT NOT NULL
);

CREATE TABLE products (
  sku         TEXT PRIMARY KEY,
  name        TEXT NOT NULL,
  brand       TEXT,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  price_cents INTEGER,
  currency    TEXT,
  moq         INTEGER,
  unit        TEXT,
  description TEXT
);

CREATE TABLE attribute_defs (
  id         INTEGER PRIMARY KEY,
  code       TEXT UNIQUE NOT NULL,   -- 'voltage', 'material'
  label      TEXT NOT NULL,
  value_type TEXT NOT NULL CHECK (value_type IN ('enum','number','text')),
  unit       TEXT
);

CREATE TABLE attribute_values (
  product_sku  TEXT NOT NULL REFERENCES products(sku),
  attribute_id INTEGER NOT NULL REFERENCES attribute_defs(id),
  value_text   TEXT,
  value_num    REAL,
  PRIMARY KEY (product_sku, attribute_id)
);

CREATE INDEX idx_av_attr_text ON attribute_values(attribute_id, value_text);
CREATE INDEX idx_av_attr_num  ON attribute_values(attribute_id, value_num);
CREATE INDEX idx_products_cat ON products(category_id);

One EXISTS per active filter keeps each condition index-friendly and avoids the duplicate-row trap you get from joining attribute_values several times:

-- placeholders: category 7, voltage=[24,48], length between 1000 and 3000 mm
SELECT p.sku, p.name, p.price_cents
FROM products p
WHERE p.category_id = 7
  AND EXISTS (
        SELECT 1 FROM attribute_values av
        WHERE av.product_sku = p.sku AND av.attribute_id = 3
          AND av.value_text IN ('24','48'))
  AND EXISTS (
        SELECT 1 FROM attribute_values av
        WHERE av.product_sku = p.sku AND av.attribute_id = 5
          AND av.value_num BETWEEN 1000 AND 3000)
ORDER BY p.name
LIMIT 50;

Facet counts come from the same predicates, grouped by attribute value. They are cheap on a few thousand rows and stop being cheap later, so cache the per-category counts and invalidate on import rather than computing them on every keystroke.

Step 8 — Test the catalog before anyone else sees it

• [ ] Every category has at least one product, and no product points at a missing category.
• [ ] Every filterable attribute has a controlled vocabulary — no free text survived import.
• [ ] A filter that returns zero results shows a suggestion, not a blank page.
• [ ] Two filters that cannot co-occur (e.g. two incompatible connector types) return zero cleanly, with an obvious way to undo one.
• [ ] Part numbers with a hyphen, slash, or space survive search intact.
• [ ] Units are consistent across the whole catalog; nothing is stored as 24V in one row and 24 in another.
• [ ] Filtered URLs are shareable and the back button restores the previous filter set.
• [ ] Sorting by price is stable when prices tie — otherwise rows jump between pages.
• [ ] Every SKU has an image, or a deliberate placeholder rather than a broken link.
• [ ] The mobile filter drawer can be opened, applied, and dismissed without scrolling to the footer.

Pitfalls worth writing on the wall

• Category as attribute. Red is not a category. Industrial is not a category. They are filters that got promoted.
• Variant as product. Six SKUs flattened into one row means five of them are invisible to search.
• Float money. 19.99 will eventually fail an equality check. Integer minor units.
• Counts computed from the unfiltered set. Visitors trust the number next to the checkbox, and they will notice.
• Free-text attribute fields. One typo, one split facet, one filter that silently hides half the catalog.
• No empty state. Zero results is a legitimate answer; silence is not.

The catalog is done when a visitor holding a number that is not in your system can still reach a definite answer in two clicks — either the equivalent, or a clear no.