Estrutura de banco de dados para produtos/variantes/pacotes/carrinhos

Você pode criar as seguintes tabelas e índices

-- 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);

Seu problema (o "pressentimento" que você tem) está relacionado a este design desnormalizado:

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

Isso permite cartItemsque sejam dois tipos de coisas completamente diferentes: um pacote e um único produto. Este é um exemplo de uma Associação Polimórfica. Seu design existente só pode ser retificado pelo uso de uma CHECKrestrição

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

Isso pode tornar esta tabela um tanto difícil de usar, pois a junção a ela pode se tornar condicional.

Projetos alternativos incluem:

• Cada ProductVariant é listada individualmente no carrinho, mas algumas também estão associadas a pacotes.

Faça product_ide variant_idobrigatório (não anulável) e tenha os três pares (product_id, variant_id, bundle_id)com chave estrangeira para bundleItems.

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

Nessa nota, bundleItemsé uma tabela de junção e, portanto, não deve ter uma idcoluna separada. Em vez disso, sua chave primária deve ser uma chave composta feita dessas mesmas três colunas.

Este design também tem a desvantagem de não ser possível impor a adição de todo o pacote, apenas parte dele.

• Separe esses dois tipos de produtos em duas tabelas distintas:

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"
}

_{Observe a falta de uma idcoluna, pois as colunas compostas denotam a chave primária.}

Esse design é o mais normalizado, mas pode ser um pouco difícil de consultar, pois você precisa consultar as duas tabelas separadamente e unir os resultados.

• Faça de cada produto um pacote com pelo menos um item, então um carrinho só poderá conter pacotes, não itens.

Isso pode ser bastante pesado em termos de armazenamento se houver apenas alguns itens que realmente precisam ser agrupados, mas é o mais fácil de consultar.