Great question! Your proposed database structure for product variants (attributes + options) is totally on the right track—it’s scalable, avoids redundant data, and follows standard e-commerce schema patterns. Let’s walk through how to implement and manage this with database migrations, covering both raw SQL and a framework example (since migrations are often tied to development frameworks).

First: Validating Your Schema #

Let’s quickly confirm why your setup works:

• Products: Stores core product details (title, etc.)—one row per base product.
• Attributes: Shared, reusable attributes (e.g., "Color", "Size")—no need to redefine "Color" for every product.
• Options: Attribute-specific choices (e.g., "Red", "XL")—linked directly to their parent attribute.
• product_attribute_option: The critical join table that ties products to attribute-option pairs, storing variant-specific data like price, stock, and sale price. This is where your unique variants live.

Managing This with Database Migrations #

Migrations are version-controlled scripts for your database structure—they ensure every environment (dev, test, production) has the exact same schema, and let you roll back changes safely.

1. Raw SQL Migrations (No Framework) #

If you’re working without a framework, create sequential SQL scripts (named by order of execution) to build your tables.

Step 1: Create Base Tables (Migration Script 001)

-- 001_create_core_tables.sql
CREATE TABLE IF NOT EXISTS Products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE, -- Ensures no duplicate attribute names
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    attribute_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (attribute_id) REFERENCES Attributes(id) ON DELETE CASCADE
);

Step 2: Create Variant Join Table (Migration Script 002)

-- 002_create_variant_join_table.sql
CREATE TABLE IF NOT EXISTS product_attribute_option (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    attribute_id INT NOT NULL,
    option_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2) DEFAULT NULL,
    qty INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Prevent duplicate variants (same product + attribute + option)
    UNIQUE KEY unique_variant (product_id, attribute_id, option_id),
    
    -- Cascading deletes: Remove variants if parent product/attribute/option is deleted
    FOREIGN KEY (product_id) REFERENCES Products(id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_id) REFERENCES Attributes(id) ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES Options(id) ON DELETE CASCADE
);

Migration Workflow

• Run scripts in numerical order (001 first, then 002) in every environment.
• If you need to modify the schema later (e.g., add a sku to variants), create a new script (003_add_sku_to_variants.sql) instead of editing old ones—this preserves version history.

2. Framework-Based Migrations (Example with Laravel) #

Most modern frameworks have built-in migration tools. Let’s use Laravel as an example (it’s widely used for e-commerce).

Generate & Run Migrations

Use Artisan commands to create migration files, then define your schema in PHP:

Products Table Migration

// Generated with: php artisan make:migration create_products_table
public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string('title');
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('products');
}

Attributes Table Migration

// Generated with: php artisan make:migration create_attributes_table
public function up()
{
    Schema::create('attributes', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('attributes');
}

Options Table Migration

// Generated with: php artisan make:migration create_options_table
public function up()
{
    Schema::create('options', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        // Foreign key with cascading delete
        $table->foreignId('attribute_id')->constrained()->onDelete('cascade');
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('options');
}

Variant Join Table Migration

// Generated with: php artisan make:migration create_product_attribute_option_table
public function up()
{
    Schema::create('product_attribute_option', function (Blueprint $table) {
        $table->id();
        $table->foreignId('product_id')->constrained()->onDelete('cascade');
        $table->foreignId('attribute_id')->constrained()->onDelete('cascade');
        $table->foreignId('option_id')->constrained()->onDelete('cascade');
        $table->decimal('price', 10, 2);
        $table->decimal('sale_price', 10, 2)->nullable();
        $table->integer('qty')->default(0);
        $table->timestamps();
        
        // Enforce unique variants
        $table->unique(['product_id', 'attribute_id', 'option_id']);
    });
}

public function down()
{
    Schema::dropIfExists('product_attribute_option');
}

Execute Migrations

• Run php artisan migrate to apply all pending migrations.
• Roll back the last migration with php artisan migrate:rollback.
• For schema changes later (e.g., adding a weight field), create a new migration: php artisan make:migration add_weight_to_product_attribute_option_table and define the change in its up method.

Key Tips for Your Schema #

• Unique Constraint: The unique_variant key is non-negotiable—it prevents duplicate variant entries for the same product-attribute-option combo.
• Cascading Deletes: Adjust ON DELETE CASCADE to ON DELETE RESTRICT if you want to block deletion of attributes/options that are linked to active product variants (avoids accidental data loss).
• Extensibility: If you need multi-attribute variants (e.g., "Red + XL" as a single variant), you can modify the join table to use a composite key of product_id + multiple option_ids, but your current setup works perfectly for single-attribute variants and can be extended easily.

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