Mateusz Sowiński Mateusz Sowiński - 1 year ago 63
SQL Question

Designating a complex SQL structure

I have to designate a pretty complicated SQL structure.

I'm making a product distribution panel, where you can order e-cigarette liquids from a company.

Each liquid has it's own volume, nicotine in miligrams, name and brand.
I want to make the administration panel easy to use. Admin would just add a product with a name and a brand, and then add volumes, and power(nicotine).

I was thinking about making a main table with products (brand and name), and then tables with volume and power, but it's a really bad idea, when I think about it... Also making a column in a table for power, and then making a set of powers a string, like 10mg, 20mg, 18mg would be a mistake.

How should I do this?
Thanks in advance.

Answer Source

According to the new requirements I think it would be better to have the db schema as described on the picture.


You have products table and volumes, powers and nicotine tables. If a product from product table has either volume, power or nicotine, then in the corresponding table there is a record with the relevant product_id.

One brand may have many products, every product may have or may not have nicotine, power or volume property. For instance if a product has nicotine property, then in nicotine table there is a record with this product_id. If not, the nicotine table will not contain the record with this product_id. The same rule applies to powers and volumes.