How To Store Arbitrarily Wide Tree Of Set Depth

Question

Hello Everyone.

I need to store data that looks like this:

<root>
    <child-one>
        value 1
        value 2
        ...
        value n
    </child-one>
    ...
</root>

By this I mean, a very shallow tree with a variable number of leaves.

I would have liked to store this data in a relational database, but I cannot find of way of doing so without:

Using many "join" tables

For example:

(Object (root, child-one, child-two, ...))

(Join1 (child-one, value 1), (child-one, value 2), ... (child-one, value n) )

(Join2 (child-two, value 1), (child-two, value 2), ... (child-two, value n) )

etc. 

Using the "array" datatype (coming from Postgresql)

(Object (root, child-one[], child-two[] ...))

Which of these two would be the best choice, if I did not allow for the use of an XML store or a Document store? Is there another strategy I'm missing?

Thank you.

Answer 1

Could you use the following schema?

Root

id | rootName
---|---------
 0 | root1

Child

id | childName | fk_root
---|-----------|--------
 0 | child-one |    0
 1 | child-two |    0

Values

id | valueData | fk_child
---|-----------|--------
 0 |   value1  |    0
 1 |   value2  |    0
 2 |   value3  |    1

That would result in/from:

<?xml ?>
    <root1>
        <child-one>
            <value1 />
            <value2 />
        </child-one>
        <child-two>
            <value3 />
        </child-two>
     </root1>