I need a new perspective on how to design a reliable and efficient SQL database to store multi-level arrays of data.
This problem applies to many situations but I came up with this example:
There are hundreds of products. Each product has an undefined number of parts. Each part is built from several elements.
All products are described in the same way. All parts would require the same fields to describe them (let's say: price, weight, part name), all elements of all parts also have uniform design (for example: element code, manufacturer). Plain and simple.
One element may be related to only part, and each part is related to one product only.
I came up with idea of three tables:
Products:
--------------------------------------------
prod_id prod_name prod_price prod_desc
1 hoover 120 unused
next
Parts:
----------------------------------------------------
part_id part_name part_price part_weight prod_id
3 engine 10 20 1
and finally
Elements:
---------------------------------------
el_id el_code el_manufacturer part_id
1 BFG12 GE 3
Now, select a desired product, select all from PARTS where prod_id is the same, and then select all from ELEMENTS where part_id matches - after multiple queries you've got all data.
I'm just not sure if this is the right approach.
I've got also another idea, without ELEMENTS table.
That would decrease queries but I'm a bit afraid it might be lame and bad practice.
Instead of ELEMENTS table there are two more fields in the PARTS table, so it looks like this:
part_id, part_name, part_price, part_weight, prod_id, part_el_code, part_el_manufacturer
they would be text type, and for each part, information about elements would be stored as strings, this way:
part_el_code | code_of_element1; code_of_element2; code_of_element3
part_el_manufacturer | manuf_of_element1; manuf_of_element2; manuf_of_element3
Then all we need is to explode() data from those fields, and we get arrays, easy to display.
Of course this is not perfect and has some limitations, but is this idea ok?
Or should I just go with the first idea?
Or maybe there is a better approach to this problem?
It's really hard to describe it in few words, and that means it's hard to search for answer.
Also, understanding the principles of designing databases is not that easy as it seems.
