Amazon sells 350 million products. A shoe has size, color, and material. A TV has screen size, resolution, refresh rate, and HDR type. A book has ISBN, author, and page count. No two product categories share the same attributes. A relational table with a column per attribute would have thousands of columns, almost all null for any given product. Variable-attribute product catalog is the data modeling problem of storing structured but heterogeneous data efficiently.

The EAV Anti-Pattern#

Entity-Attribute-Value (EAV): three tables: products(id, name), attributes(id, name), product_attributes(product_id, attribute_id, value). Flexible, but querying is painful. “Find all TVs with refresh rate > 120Hz” requires joining product_attributes twice and filtering. With 350 million products and hundreds of attributes per product, EAV tables grow to tens of billions of rows. Query performance degrades severely.

EAV is the wrong default despite its apparent flexibility.

Document Model#

Store product attributes as a JSON/BSON document in a document database (MongoDB, DynamoDB) or as a JSON column in PostgreSQL. Each product has a schema-free attributes blob:

{"screen_size": 65, "resolution": "4K", "refresh_rate": 120, "hdr": "Dolby Vision"}

Writes are fast (no schema migration for new attributes). Reads of a single product are fast. The challenge: filtering and searching across millions of products on arbitrary attributes.

graph TD A[Product: 65-inch TV] --> B[Core table: product_id, name, category, price] A --> C[Attributes doc: screen_size=65, resolution=4K, refresh_rate=120] D[Query: TVs refresh_rate over 120Hz] --> E[Category index: filter to TVs] E --> F[Attribute index on refresh_rate for TV category] F --> G[Return matching products] style A fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style B fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style D fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style E fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style F fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style G fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

Category-Scoped Indexes#

You can’t index every attribute for every product. Index per category: TVs have an index on refresh_rate, shoes have an index on size and color. This is manageable because the attribute set per category is fixed and known.

In practice: a category_id scopes all queries. Attribute indexes are only built for attributes within a category. A query for “TVs with refresh_rate > 120” hits the TV-category attribute index, not a global attribute index.

Separation of Catalog and Inventory#

The product catalog (attributes, descriptions, images) is separate from inventory (how many are in stock at which warehouse). Catalog data is write-rare, read-heavy, and globally shared. Inventory is write-frequent, location-specific, and needs stronger consistency. Keeping them in separate stores with separate scaling strategies is the standard architecture.

At Oracle#

Oracle Commerce Cloud (now part of Oracle CX) handled exactly this problem for B2B catalogs: industrial parts catalogs where one bolt has 12 specifications and one motor has 80. We used a hybrid model: fixed schema for universal attributes (name, category, price, supplier ID) and a JSON column for category-specific attributes. Category-specific search queries used PostgreSQL’s GIN indexes on JSONB columns. Query performance was acceptable up to 2 million products per category; above that, we moved category-specific attributes to Elasticsearch.

What I’m Learning#

Variable-attribute catalogs are a schema flexibility vs query performance trade-off. The document model solves the schema problem but creates indexing complexity. Category-scoped indexes are the practical solution: you give up on querying across all products by arbitrary attributes, but that query was never fast anyway.

Have you designed product catalogs or other variable-schema data stores, and where did the schema flexibility trade-off bite you?