Posted by Stefan Rybacki on 10/12/34 11:25

Hilarion wrote:
>>>> 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

Stefan

PS: its similar to what Evert pointed to.


>
> Hilarion
>
> 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.

[Back to original message]