产品/变体/捆绑包/购物车的数据库结构

您可以创建下表和索引

-- Products Table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- Index on the name column for quicker search by product name
CREATE INDEX idx_product_name ON products(name);

-- Variants Table
CREATE TABLE variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    product_id INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Index on product_id to optimize joins between products and variants
CREATE INDEX idx_variant_product_id ON variants(product_id);

-- Product-Variant Relationships Table (Optional, if different variants have different prices or other unique attributes)
CREATE TABLE product_variants (
    product_id INT NOT NULL,
    variant_id INT NOT NULL,
    price DECIMAL(10, 2),  -- Optional field
    PRIMARY KEY (product_id, variant_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES variants(id) ON DELETE CASCADE
);

-- Index on price for filtering based on variant price
CREATE INDEX idx_product_variant_price ON product_variants(price);

-- Bundles Table
CREATE TABLE bundles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- Bundle Items Table
CREATE TABLE bundle_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bundle_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    FOREIGN KEY (bundle_id) REFERENCES bundles(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES variants(id) ON DELETE CASCADE
);

-- Indexes for bundle_items to speed up joins and searches on bundle_id, product_id, and variant_id
CREATE INDEX idx_bundle_items_bundle_id ON bundle_items(bundle_id);
CREATE INDEX idx_bundle_items_product_id ON bundle_items(product_id);
CREATE INDEX idx_bundle_items_variant_id ON bundle_items(variant_id);

-- Carts Table
CREATE TABLE carts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,  -- Optional: If associated with a specific user
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index on user_id for quick retrieval of carts associated with a specific user
CREATE INDEX idx_cart_user_id ON carts(user_id);

-- Cart Items Table
CREATE TABLE cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL,
    product_id INT,
    variant_id INT,
    bundle_id INT,  -- If item is from a bundle
    quantity INT NOT NULL DEFAULT 1,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES variants(id) ON DELETE CASCADE,
    FOREIGN KEY (bundle_id) REFERENCES bundles(id) ON DELETE SET NULL
);

-- Indexes for cart_items to speed up joins and searches on cart_id, product_id, variant_id, and bundle_id
CREATE INDEX idx_cart_items_cart_id ON cart_items(cart_id);
CREATE INDEX idx_cart_items_product_id ON cart_items(product_id);
CREATE INDEX idx_cart_items_variant_id ON cart_items(variant_id);
CREATE INDEX idx_cart_items_bundle_id ON cart_items(bundle_id);

-- Discounts Table
CREATE TABLE discounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    discount_type ENUM('PERCENTAGE', 'ABSOLUTE') NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    applicable_type ENUM('BUNDLE', 'CART', 'PRODUCT') NOT NULL,
    applicable_id INT NOT NULL,
    precedence INT DEFAULT 1,
    FOREIGN KEY (applicable_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Indexes on applicable_type and applicable_id for faster filtering by type and associated entity
CREATE INDEX idx_discounts_applicable_type ON discounts(applicable_type);
CREATE INDEX idx_discounts_applicable_id ON discounts(applicable_id);
CREATE INDEX idx_discounts_precedence ON discounts(precedence);

您的问题（您的“直觉”）与这种非规范化的设计有关：

cartItems: {
    id: "INTEGER",
    cart_id: "INTEGER",
    discount_type: "PERCENTAGE|ABSOLUTE",
    discount: "INTEGER",
    product_id: "INTEGER",
    variant_id: "INTEGER",
    bundle_id: "INTEGER",
    quantity: "INTEGER"
}

这允许cartItems两种完全不同的事物：捆绑和单一产品。这是多态关联的一个例子。您现有的设计只能通过使用CHECK约束来纠正

CHECK (product_id IS NOT NULL AND variant_id IS NOT NULL OR bundle_id IS NOT NULL)

这可能会使该表使用起来有些笨重，因为与其的连接可能是有条件的。

替代设计包括：

• 每个产品变量都在购物车中单独列出，但有些也与捆绑包相关联。

使product_id和成为variant_id强制性的（不可空），并将三对(product_id, variant_id, bundle_id)作为外键添加到bundleItems。

FOREIGN KEY (product_id, variant_id, bundle_id) REFERENCES bundleItems (product_id, variant_id, bundle_id)

需要注意的是，bundleItems它是一个连接表，因此不应该有单独的id列。相反，它的主键应该是由相同的三列组成的复合键。

这种设计也有一个缺点，那就是你不能强制添加整个Bundle，而只能添加其中的一部分。

• 将这两类产品分成两个单独的表格：

cartProducts: {
    cart_id: "INTEGER",
    product_id: "INTEGER",
    variant_id: "INTEGER",
    discount_type: "PERCENTAGE|ABSOLUTE",
    discount: "INTEGER",
    quantity: "INTEGER"
}

cartBundles: {
    cart_id: "INTEGER",
    bundle_id: "INTEGER",
    discount_type: "PERCENTAGE|ABSOLUTE",
    discount: "INTEGER",
    quantity: "INTEGER"
}

_{请注意缺少一id列，因为复合列表示主键。}

这种设计是最规范化的，但查询起来可能有点笨拙，因为您需要分别查询两个表并合并结果。

• 使每个产品至少包含一个物品的捆绑包，然后购物车只能包含捆绑包而不能包含物品。

如果实际上只有少数项目需要捆绑，那么存储方面可能会相当繁重，但却最容易查询。