What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of product B is 30kg

The following is my initial design.

product:
id       name

product_variation:
id    product_id      name        price

product_variation_unit:
id          product_variation_id             unit_id

product_variation_unit_weight:
product_variation_unit_id       weight

The problem is than most of the variation of a product have the same unit and weight. So here is my solution but I’m not sure it’s a good idea:

selectable_unit:
id          selectable_id     selectable_type        unit_id
1            10               product                100
2            20               product_variation      101

selectable_unit_weight:
selectable_unit_id       weight

And when I want to get the unit and weight of a variation, first I will check the variation and if there isn’t, I will check it’s product.

I’t a little complicated, can you help me to find a better solution or improve it?

It’s a 1:many relationship. That is “one” product has “many” weights (or bundles or whatever).

With that, you need 2 tables; one for the product and its attributes (other than the bundling); one for the bundling info.

The “metric” (eg, kg) could be in either table — Make the decision based on whether all products have a single metric, then put it in the product table. Else put it in the bundling table. Even though there is a lot of redundancy (“kg” showing up in may rows), do not normalize it.

Please spell out table in CREATE TABLE syntax — this avoid leaving out details, and makes things clearer:

CREATE TABLE  Products (
    prod_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name ..., (etc)
    PRIMARY KEY(prod_id)
);

CREATE TABLE Bundles (
    prod_id INT UNSIGNED NOT NULL,
    metric VARCHAR(10) CHARACTER SET ascii NOT NULL,
    qty FLOAT NOT NULL,
    PRIMARY KEY(prod_ic, metric, qty)
);