How to prevent recursiveness in a tree

[Magentix]

Simple situation:
- Object A, B and C have an ID, some properties and a 'parent' property
- C has B as a parent, B has A as a parent and all is well in the application's kingdom
- In comes the evil witch who cast a vile spell making A have C as a parent
- Horror ensues

Or, in regular English:
How do I prevent (or check for) recursiveness in a MySQL database in a parent-child dictionary-like data tree.

Example table fields:
- ID (int ai pk)
- term (varchar)
- parent (int)

 

[phyrex1an]

You could create a database trigger that checks that the "no cycles" property holds before each update.
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

You could use another method for storing trees than the "object has a parent".
http://www.sitepoint.com/hierarchical-data-database/
You can delete the parent column and still retain the structure of the tree, you still need to check that the tree property holds on each update but it is much easier to do with the lft and rgt columns than with just a parent column.

You could add a depth column in addition to the parent column and enforce that the depth of a child must be exactly one higher than the depth of a parent.

 

[Magentix]

Sweet, I'll look into this tomorrow. <3

 

[The Helper]

A phyrex1an thread!