What is the best approach in SQL to store multi-level descriptions?

Posted by gime on Stack Overflow See other posts from Stack Overflow or by gime
Published on 2010-12-28T04:15:42Z Indexed on 2010/12/28 4:53 UTC
Read the original article Hit count: 160

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about arrays