多品类商品信息存储方案咨询：架构合理性与优化建议

Hey there! Let's break down your question step by step—building a multi-category e-commerce app with diverse product attributes is a classic (and tricky) problem, so your thought process on the three-table schema is already a solid starting point. Let's dive into each of your questions:

1. Is your proposed schema correct? #

Short answer: Yes, it's logically correct for your use case. Here's why:

• The products table with nested sets neatly handles hierarchical categories and stores universal metadata (name, SKU, prices) that applies to every product—this makes sense for shared attributes.
• The EAV-style products_props and products_props_data tables solve the core problem of variable attributes across categories (jeans vs. laptops have totally different fields).

That said, it's not without tradeoffs (we'll get to efficiency next), but it's a valid approach for handling multi-category product data.

2. Is this schema efficient? #

Since you noted read operations far outnumber write operations, let's split this into read and write efficiency:

Read Efficiency #

• Good parts:
  • Querying universal product metadata (price, SKU) from the products table is fast, as it's a flat, indexed table.
  • Nested sets are great for fast category-based queries (e.g., "get all products in the 'Jeans' category")—the lft/rgt range query avoids recursive joins, which is much faster than adjacency list models for deep hierarchies.
• Pain points:
  • Complex attribute-based queries (e.g., "find all blue, size L jeans with cotton material") will require multiple joins between products, products_props_data, and products_props. As you add more attributes to filter on, the query becomes slower and harder to maintain.
  • Aggregation queries (e.g., "how many products have a 'processor' attribute equal to 'Intel i7'") are cumbersome with EAV, as you have to pivot data or use subqueries.

Write Efficiency #

• Since writes are rare, the overhead of inserting multiple products_props_data rows per product is manageable. However, modifying category hierarchies with nested sets is expensive (you have to update lft/rgt values for all affected nodes)—but again, if category changes are infrequent, this isn't a dealbreaker.

3. Are there other feasible solutions? #

Absolutely! Here are the most common alternatives, each with their own pros and cons:

Option 1: JSON/JSONB Columns (PostgreSQL) or JSON Columns (MySQL) #

• Store variable product attributes in a single JSON/JSONB column in the products table (alongside universal metadata).
• Pros: Flexible schema, easy to add new attributes, fast to read a single product's full attributes, and PostgreSQL's JSONB supports GIN indexes for efficient attribute filtering.
• Cons: Complex cross-product attribute queries (e.g., filtering by multiple attributes) are slower than structured EAV, and you lose strict data type enforcement (unless you add application-level validation).

Option 2: Sparse Columns in a Single Table #

• Add all possible product attributes as columns to the products table, allowing NULL values for attributes that don't apply to a category.
• Pros: Simple to query, no joins needed for attribute data.
• Cons: The table becomes extremely wide as you add more categories, leading to wasted storage and slower query performance. Not scalable for large numbers of unique attributes.

Option 3: Category-Specific Tables #

• Create separate tables for each major category (e.g., jeans, laptops, groceries) that inherit universal fields from a base products table, plus category-specific attributes.
• Pros: Fast queries for category-specific products, strict data type enforcement, easy to add category-specific constraints.
• Cons: Adding a new category requires schema changes, cross-category queries become complex (unioning multiple tables), and codebase complexity increases.

Option 4: Hybrid Approach #

• Combine structured tables and flexible storage:
  • Keep universal metadata in the products table.
  • Store frequently filtered attributes (size, color, brand) in a structured EAV or dedicated join table.
  • Store rare, niche attributes in a JSONB column.
• Pros: Balances query efficiency for common attributes and flexibility for niche ones.
• Cons: Adds some complexity to your data model and code.

4. Recommendations for implementing your requirement #

If you stick with your three-table EAV + nested set schema: #

• Index strategically:
  • Add a unique composite index on products_props_data (product_fk, product_prop_fk) to prevent duplicate attribute entries for a single product.
  • Add composite indexes on products_props_data (product_prop_fk, value) for frequently filtered attributes (e.g., size, color) to speed up WHERE clauses.
• Cache aggressively: Since reads dominate, use a caching layer (Redis, Memcached) to store results of common queries:
  • Category product lists
  • Popular attribute filters (e.g., all available sizes for jeans)
  • Product detail pages (including their attributes)
• Enforce attribute consistency: Use the products_props table to strictly define attributes (avoid duplicate names like "Size" and "size"), and add application-level validation to ensure attribute values match the defined input_type (e.g., numeric values for "storage").

If you consider switching to a hybrid/JSONB approach: #

• Go with PostgreSQL if possible: Its JSONB type is far more performant than MySQL's JSON, with support for indexing and querying nested attributes.
• Simplify category storage: If category hierarchies don't change often, nested sets are fine. If they do, use an adjacency list (parent_id) with recursive queries (PostgreSQL's WITH RECURSIVE, MySQL 8+ supports this too)—it's easier to modify, even if queries are slightly slower.
• Abstract attribute logic: Build a service layer in your code that handles attribute retrieval/updating, regardless of the underlying storage. This way, you can switch between EAV, JSONB, or other models later without rewriting all your application code.

5. How do top e-commerce platforms (Amazon, Flipkart, eBay) handle this? #

All major platforms use hybrid, highly optimized architectures tailored to their scale:

• Core product data: Universal metadata (ID, name, SKU, price, inventory) lives in structured, high-performance relational databases (often sharded by region or category) to ensure fast reads/writes for critical operations.
• Variable attributes:
  • Frequently filtered attributes (brand, size, color) are stored in structured join tables or dedicated attribute databases, with heavy indexing to support fast faceted search.
  • Niche, category-specific attributes are stored in document databases (MongoDB, Cassandra) or JSON columns, allowing flexibility without slowing down core operations.
• Category hierarchies: They use cached tree structures—often precomputing category paths and product-category mappings in memory (via Redis or custom caching layers) to avoid expensive database queries.
• Analytics: Attribute data for reporting/analytics is offloaded to data warehouses (Redshift, BigQuery), so the transactional database isn't bogged down by complex aggregation queries.

内容的提问来源于stack exchange，提问作者Nikhil Bhatia