T
T
tripcollor2018-10-25 11:33:32
Database design
tripcollor, 2018-10-25 11:33:32

How to design a database of item content in products?

There is a table for the content of microelements in food products
(Content of an element in 100 g of product)
5bd179bb2b4a6776758340.png
As I understand it, I should have a separate table with food and a separate table with microelements and then somehow correlate them and enter the content of each substance in each product. Or should I still have one table with columns of all elements, roughly speaking, as in the picture? Or maybe make a field with the json type?
It is important that in the future it will be possible to conveniently work with this by adding data through the site admin panel. Plus, it is also planned that this functionality will be further expanded and interact with other modules on the site.
What is the best way to store data in this format?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
I
Ivan Shumov, 2018-10-25
@tripcollor

Related tables.
- product
table - element
table - dimension
table - union table (reference to product, element, dimension and value)
Ranges are very confusing, but they can be written as from-to

S
sim3x, 2018-10-25
@sim3x

If items don't change often then make one table with 20 fields per product (those don't do EAV) - null doesn't take up much space
Cast all values ​​to C (those to kg) and cast them out-of-system already in the interface

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question