时尚聚合网站电商产品与变体数据库表填充方案咨询

First off, splitting your single products table into separate parent and variant tables is the perfect fix for those slow queries—your original approach created massive data redundancy, which kills database performance. Let’s walk through the schema design, data processing steps, and best practices to make this work smoothly.

1. Database Schema Design #

You’ll need two tables with a clear one-to-many relationship (one parent product → many variants). The key here is adding proper indexes on the foreign key to ensure fast lookups.

Parent Products Table (parent_products) #

This holds shared attributes that don’t change across variants (no redundant data here):

CREATE TABLE parent_products (
    parent_id UUID PRIMARY KEY, -- Or use auto-increment INT if you prefer
    product_name VARCHAR(255) NOT NULL,
    brand VARCHAR(100),
    description TEXT,
    main_image_url VARCHAR(255),
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Product Variants Table (product_variants) #

This stores variant-specific attributes, with a foreign key linking back to its parent. We’ll add an index on parent_id to make "get all variants for a parent" queries lightning fast:

CREATE TABLE product_variants (
    variant_id UUID PRIMARY KEY,
    parent_id UUID NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_status BOOLEAN DEFAULT TRUE, -- True = in stock, False = out
    size VARCHAR(50),
    color VARCHAR(50),
    sku VARCHAR(100) UNIQUE NOT NULL, -- Critical for unique variant identification
    variant_image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES parent_products(parent_id),
    INDEX idx_parent_id (parent_id) -- This is non-negotiable for fast queries
);

2. Process Crawler Data (CSV/Pandas DataFrame) #

Assuming your crawler outputs a flat dataset where each row is a variant (with repeated parent attributes), here’s how to split and map this to the two tables using pandas:

Step 1: Extract & Deduplicate Parent Products #

First, identify unique parent products by grouping on attributes that define a single parent (e.g., product_name + brand—use whatever unique combination your crawler provides):

import pandas as pd
import uuid

# Load your crawler data into a DataFrame
df = pd.read_csv("crawled_products.csv")

# Group by parent-unique attributes to get distinct parent products
parent_groups = df.groupby(["product_name", "brand"])

# Extract shared parent attributes (take the first row's value from each group)
parent_df = parent_groups[["product_name", "brand", "description", "main_image_url", "category"]].first().reset_index()

# Generate unique parent IDs (UUIDs work great for distributed systems)
parent_df["parent_id"] = [uuid.uuid4() for _ in range(len(parent_df))]

Step 2: Map Variants to Their Parent #

Merge your original variant data with the parent DataFrame to attach the parent_id to each variant:

# Merge on the same parent-unique attributes used for grouping
variant_df = df.merge(parent_df, on=["product_name", "brand"], how="left")

# Keep only variant-specific attributes + the parent_id
variant_df = variant_df[["parent_id", "price", "stock_status", "size", "color", "sku", "variant_image_url"]]

# Generate unique variant IDs
variant_df["variant_id"] = [uuid.uuid4() for _ in range(len(variant_df))]

Step 3: Bulk Insert into Database #

Use pandas’ to_sql method for efficient bulk insertion (much faster than row-by-row):

from sqlalchemy import create_engine

# Replace with your database connection string
engine = create_engine("postgresql://user:password@host:port/db_name")

# Insert parent products first (variants depend on them)
parent_df.to_sql("parent_products", engine, if_exists="append", index=False)

# Insert variants
variant_df.to_sql("product_variants", engine, if_exists="append", index=False)

3. Why This Fixes Your Slow Queries #

• No Redundancy: Parent product data is stored once, not 24 times for every variant—this reduces table size and speeds up scans.
• Indexed Foreign Key: The idx_parent_id index lets the database instantly locate all variants for a parent product, instead of scanning the entire table like your old single-table setup.
• Cleaner Queries: Fetching all variants for "夏季连衣裙" becomes a simple, fast query:

  SELECT * FROM product_variants WHERE parent_id = 'your-parent-uuid';
  

Pro Tips #

• If your crawler returns a unique parent product ID (instead of relying on name/brand), use that directly to avoid grouping errors.
• Always validate that sku values are unique across variants—this prevents duplicate entries.
• For large datasets, wrap insertions in a database transaction to ensure data consistency if something fails mid-insert.

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