1. Should the top level parent node have parent_id values of 0 or NULL,
from a normalisation perspective, one would guess that the parent should
be 0 so that all values in the col are of the same type, however by
using 0 you are effectively creating a reference to a non-existant row -
any feedback on this behavior would be greatly appreciated
I think top level nodes should have a parent_id of 0. It may be
referring to a non-existent row but you will need special code to handle
the top-level nodes irrespective of using 0 or NULL anyway.
2. This question is with regards to handling deletion of nodes that have
children, it would seem much more sensible to move the child nodes of
the deleted node up to the grandparent node, rather than breaking the
chain of parent references or deleting the child nodes completely, is
this the best way to approach it?
Do not attach them to the parent. I think that would be undesirable and
unexpected in most cases. I would set the parent_id to NULL to indicate
that the node that was deleted is now "orphaned". You can then query
orphaned nodes and re-attach them to another node if desired.
Christoph
