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

Posted on

Question :

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?

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *