Database structure for Products/variants/bundles/carts

I got a scenario where i need to design a SQL db structure over MariaDB but i am not a Database Architect and i want to do it right the first try.

So, i have a products table which, for the sake of readability, has 2 fields, id and name. I have no idea how to make this graphic so i'll use json to define it:

productColumns = {
    id: "INTEGER",
    name: "STRING",
}

Then i have the variants table:

variantsColumns = {
    id: "INTEGER",
    name: "STRING",
    product_id: "INTEGER",
}

Now the goal. I need to add a Bundles and Carts. Bundles have to contain multiple products with a specified variant. Carts have to contain multiple products with a specified variant and also bundles. Now i need the ability to also add discounts to carts and bundles as a whole but also to add discounts to specific products from the cart or from the bundle.

What i thought:

bundlesColumns: {
    id: "INTEGER",
    name: "STRING",
    discount_type: "PERCENTAGE|ABSOLUTE"
    discount: "INTEGER"
}

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

carts: {
    id: "INTEGER",
    discount_type: "PERCENTAGE|ABSOLUTE"
    discount: "INTEGER"
}

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

I have designed a few databases before but something about this structure feels off, looks like it's gonna be a pain in the *** for future development and wanted to get your opinion on this. Is there a better way to do this?

Solution

Your issue (the "gut feeling" you have) is related to this denormalized design:

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

This is allowing cartItems to be two completely different types of things: a bundle and a single product. This is an example of a Polymorphic Association. Your existing design can only be rectified by the use of a CHECK constraint

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

This can make this table somewhat unwieldy to use, as joining to it can become conditional.

Alternative designs include:

Every ProductVariant is listed individually on the Cart, but some are associated with Bundles as well.

Make product_id and variant_id compulsory (non-nullable) and have the three-pair (product_id, variant_id, bundle_id) be foreign-keyed to bundleItems.

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

On that note, bundleItems is a join table, and therefore should not have a separate id column. Instead it's primary key should be a composite key made up of those same three columns.

This design also has the downside that you cannot enforce the whole Bundle is added, only part of one.

Separate out these two types of products into two separate tables:

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

_{Note the lack of an id column, as the composite columns denote the primary key.}

This design is the most normalized, but can be a little bit unwieldy to query, as you need to query both tables separately and union the results.

Make every product a Bundle of at least one item, then a Cart can only contain Bundles not Items.

This can be rather heavy in terms of storage if there are only a few items that actually need to be bundled, but is the easiest to query.