Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?

The question that decides it

Candidates reach for a representation as soon as they hear "varying attributes", and the choice cannot be made from that information. The deciding question is which attributes appear in a query predicate — filtered on, sorted by, aggregated over, or constrained. Attributes that are only ever displayed on a product page, fetched by primary key alongside everything else about that product, are almost free to store in any of the shapes below. Attributes that drive a faceted search over a million rows are not, and those are the ones that determine the design.

Ask it in the interview, because the honest answer is usually that a small stable set is queried and a long tail is not. That asymmetry is what the model should follow.

Promote what you query, keep the rest as a document

CREATE TABLE product (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text   NOT NULL UNIQUE,
  category_id bigint NOT NULL REFERENCES category,
  -- Promoted: every listing page filters and sorts on it, so it gets a real
  -- type, a real constraint, and an index that works like an index.
  price_minor bigint NOT NULL CHECK (price_minor >= 0),
  attrs       jsonb  NOT NULL DEFAULT '{}'::jsonb
);

-- Containment over any key, without one index per attribute.
CREATE INDEX product_attrs_gin ON product USING gin (attrs jsonb_path_ops);

jsonb_path_ops is the deliberate choice rather than the default jsonb_ops: it indexes key-and-value pairs hashed together, so it is smaller and faster for @> containment, at the cost of not supporting the key-existence operators ?, ?& and ?|. If your queries are "products where voltage is 240V" it is the right operator class; if you need "products that mention voltage at all", stay on the default.

The properties you are trading away should be said plainly rather than discovered later. There is no NOT NULL on a JSON key, no foreign key from one, no type: {"voltage": "240"} and {"voltage": 240} are different values that render identically in a UI, and nothing stops both existing in the same column. numeric inside jsonb also has its own wrinkle, since a value like 1.10 is preserved but comparisons happen in the JSON type rather than in SQL. So writes need validation somewhere, and if it lives only in application code then every backfill script and every psql session is a hole in it.

Some of it can be pulled back with constraints. A CHECK using jsonb_typeof pins a key's type, and a category-conditional check enforces required keys:

ALTER TABLE product ADD CONSTRAINT battery_needs_voltage
  CHECK (category_id <> 7 OR attrs ? 'voltage');

That works and it does not scale to fifty categories in fifty check constraints. At that point the rule belongs in an attribute_definition table — key, category, data type, unit, whether required, allowed values — validated by a trigger or by one shared write path. Modelling the definitions as data is worth doing regardless of where the values live, because the UI needs it to render the form, the importer needs it to validate a spreadsheet, and neither should be reading a list of column names out of the catalogue.

Promoting an attribute later, without a rewrite

The reason this shape survives contact with "merchants add new ones weekly" is that promotion is cheap and does not disturb the write path. A generated column extracts the key, and the index goes on the column.

ALTER TABLE product
  ADD COLUMN screen_inches numeric
  GENERATED ALWAYS AS ((attrs->>'screen_inches')::numeric) STORED;

CREATE INDEX product_screen_inches ON product (screen_inches);

Writers keep writing attrs; readers get a typed, sortable, range-queryable column with ordinary statistics behind it. Two caveats. The expression must be immutable, which ->> plus a numeric cast is, and the cast is evaluated on every write, so a product whose screen_inches is "13 in" now fails to insert — a feature if you want the validation and an outage if you deploy it against dirty data, so backfill and check before adding the column. PostgreSQL supports stored generated columns from version 12.

The equivalent in MySQL 8 is a generated column too, and there it is the only route to indexing inside a JSON document, since MySQL has no GIN equivalent for JSON containment. MySQL added functional indexes in 8.0.13, which are generated columns under the covers, and multi-valued indexes in 8.0.17 for indexing arrays inside a JSON column. If the interviewer names MySQL, the design is the same but every queryable attribute has to be promoted individually — there is no general-purpose index over the whole document.

Where entity-attribute-value earns its place, and where it does not

The classical alternative is one row per attribute value: (product_id, attribute_id, value). It is genuinely flexible and it is genuinely expensive, and the cost has a shape worth being precise about.

Every attribute you filter on is another join. "Products where voltage is 240V and colour is black and capacity is over 5 litres" is three self-joins against the same tall table, and the planner has to estimate the cardinality of each one with statistics that describe the whole mixed table rather than any one attribute. Those estimates are usually poor, and poor estimates on a three-way self-join produce the plans that turn a facet search into a timeout. Reconstructing one product for display means a pivot, either in SQL or in the application. value is text, so numeric comparison needs a cast, ordering is lexical unless you cast, and a bad value poisons the query rather than the row. And you cannot express "voltage is required for batteries" as a constraint at all, because the absence of a row is not something a check constraint can see.

Where it still fits: when attributes are genuinely first-class entities in the domain — versioned, permissioned, individually audited, referenced by identifier from elsewhere — and when reads are overwhelmingly "give me everything about this one product". Those conditions are real, and they are much rarer than the frequency with which EAV gets built.

Against a JSONB column, EAV's one real advantage is that values are rows, so per-value provenance and a foreign key on the attribute key itself become possible. For a product catalogue that does not pay for the joins. For a clinical observation store, where each measured value carries its own unit, timestamp, method and provenance, it does — and noting that the choice turns on whether a value has attributes of its own is the observation that reads as experience.

The two shapes nobody proposes and one of them should be

A wide sparse table with three hundred nullable columns gets dismissed as naive, and it should be dismissed for a reason rather than a reflex. The reason is the rate of change: every new attribute is a DDL statement, so weekly additions from merchants mean migrations forever. Where the attribute set is large but stable, the wide table is the best of these options and not the worst, because every column has a type, a constraint and its own statistics. PostgreSQL's hard limit of 1,600 columns is the ceiling, not the objection.

Class-table inheritance — a shared product table plus one table per category holding that category's typed columns — is the option most candidates never mention and it is right more often than its reputation suggests. When there are eight categories that change once a year, eight child tables give you full typing, full constraints, and ordinary indexes, and the cost is a join and a CASE in any query that spans categories. It stops working when categories number in the hundreds or arrive weekly, which is precisely the condition in this question, so the useful move is to name it and say why it does not fit here.

The end state for most real catalogues is a hybrid, and it is worth stating as a deliberate design rather than a compromise: typed columns for the fifteen attributes that facets and business rules depend on, a JSONB document for the tail, an attribute_definition table so the tail is still validated and displayable, and a documented promotion path for when a tail attribute becomes a facet.