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