Posted by Hilarion on 10/13/99 11:25

>>>>> Hilarion I am looking at your current suggestion. I am using
>>>>> phpMyAdmin to interface with my database currently, its a MySql engine.
>>>>
>>>> On MySQL InnoDB tables (only!) you can use FOREIGN KEY with ON DELETE
>>>> CASCADE
>>>> (from version 3.23.50). To create this constraint you are required to
>>>> create
>>>> indexes on the referenced fields (separate index for "Parent" and
>>>> separate
>>>> index for "SectionID"). In versions 4.1.2 (and newer) the index on
>>>> referencing
>>>> column ("Parent") will be created automatically (if it does not exist).
>>>
>>> Wouldn't it be easier to create a better database design? Now its the
>>> deletion the next thing is the question of the whole hierarchy with
>>> one query. The OPs kind of table structure is straight forward in
>>> programming but not in a relational model where you're querying for
>>> tupels without knowing others at the same time like compare to
>>> previous tupel. It is clear that the OP could write a program to do
>>> all the necessary calls to get the whole hierarchy from the table but
>>> that is not very clever. E.g. imagine a tree like hierarchy with 3
>>> levels and 3 child nodes on every node.
>>>
>>> root
>>> node node node
>>> node node node node node node node node node
>>>
>>> This way you have to query for the first 3 nodes, then you have to
>>> query for the next nodes for each of the found nodes. Makes 4 queries
>>> for such a little tree. Imagine a tree with 5 childnodes per node and
>>> 5 levels in depth. And now imagine this tree is a menu structure on a
>>> webpage with 5 users per second. makes 20 queries per second for the
>>> example above. And 5 is not much and the tree is very small.
>>>
>>> So I suggest another table design. OP have a look at nested sets.
>>
>> You may be right, but id depends on the case. If the level of hierarchy
>> is undefined
>> (can be any level) and all tree nodes are of same structure, then
>> implementing
>> it differently is imposible without using some non-standard nested
>> tables or
>> something like that (I do not know what nested sets are but if you are
>> talking
>> about using fields storing sets of parent and/or child node identifires,
>> then it
>> doesn't make things any easier or clearer). The solution which Anon uses
>> is quite clear
>> in those cases and I do not think that complications with querying the
>> data are so much
>> pain. Some SQL engines provide ways to do hierarchical queries (like
>> mentioned DB2
>> or Oracle) making use of such structures easier.
>>
>> I may be wrong of course, so if I am, then please, give some examples of
>> better
>> database representation of a tree structure with no level limits (something
>> like folder structure) and some explanation why this representation is
>> better
>> that using parent reference in single table.
>>
> I already gave. The nested sets model ;) Its something different than you think. Have a
> look at this:
>
> http://www.developersdex.com/gurus/articles/112.asp
>
> PS: its similar to what Evert pointed to.

Thanks for the link. I was not familiar with the nested sets concept.
I find it very useful for large hierachies which are not very dynamic.
I do not think if it's something one should use for small hierarchies (like
simple menus) cause the data is harder to view (as a table) and to modify
and the performance increase is not so important with this amount of
data. It's also not something I would use on larger hierachies which
are dynamic because nested sets concept requires modifications of
many records even if modification in hierarchy structure is very
simple, which afects performance badly.


>> PS.: I'm not sure what does Anon use his structure for. If it's limited
>> level hierarchy, then maybe he should consider changing his database
>> design, but I would not assume that he should not knowing what it's
>> going to be used for.

I still think that the decision on choosing design model should be
based on the requirements, so without information from Anon theres
no way to tell which approach will be better in his case.


Hilarion

[Back to original message]