Better data-structure design

Question

Currently in my application I have a single table that is giving me a bit of trouble. The issue at hand is I have a value object that is mapped to this table. When the data is returned to me as an array of value objects, I have to then loop through this array and begin my recursion by matching the ParentID to parent ObjectID's.

The column ParentID is either null (acts a parent) or it holds the value of an ObjectID.

I know there has to be a better way to create this data structure so that I do not have to do recursive loops to match ParentID's with their ObjectID's.

Any help with this is greatly appreciated.

Here is the table in describe form:

+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field          | Type             | Null | Key | Default             | Extra                       |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| ObjectID       | int(11) unsigned | NO   | PRI | NULL                | auto_increment              |
| ObjectHeight   | decimal(6,2)     | NO   |     | NULL                |                             |
| ObjectWidth    | decimal(6,2)     | NO   |     | NULL                |                             |
| ObjectX        | decimal(6,2)     | NO   |     | NULL                |                             |
| ObjectY        | decimal(6,2)     | NO   |     | NULL                |                             |
| ObjectLabel    | varchar(255)     | NO   |     | NULL                |                             |
| TemplateID     | int(11) unsigned | NO   | MUL | NULL                |                             |
| ObjectTypeID   | int(11) unsigned | NO   | MUL | NULL                |                             |
| ParentID       | int(11) unsigned | YES  | MUL | NULL                |                             |
| CreationDate   | datetime         | YES  |     | 0000-00-00 00:00:00 |                             |
| LastModifyDate | timestamp        | YES  |     | NULL                | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+---------------------+-----------------------------+e

Answer 1

You could use a nested set model. See the very good explanation here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html