|
|
Posted by Hilarion on 10/13/82 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.
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.
Navigation:
[Reply to this message]
|