Hierarchical Data in a Relational Database

Posted in Data structure by

Relational Databases like Mysql (or similar) do not support storing xml like data natively. There are different solutions for it and we present you another one.

The table structure:

id INTEGER PRIMARY INCREMENT
name VARCHAR(50)
parent INTEGER
deepness INTEGER
orderness INTEGER

This unique table structure is less strict then all other solutions. This can be positive or negative. However it was positive for our projects, because of the flexibility. It is well suited for content management systems and breadcrumb navigations.

Adding a Element 1) Get the id of the parent Element, if none use zero. 2) Add the new Element with name, parent id. 3) deepness and orderness can be updated freely and independent of parent.

Updating a Element Just update. Nothing special to do.

Deleting a Element If you delete a Element, you still have all child elements of it. And they still can be retrieved.

deepness and orderness The deepness value tells how far to indent an element. The orderness value is used to have a custom sorting option. That is helpful, if you got an article series and can not rely on id.

retrieving all elements That is done with one single query.

SELECT * FROM tablename ORDER BY orderness

Thats it.

Published at , Updated at 2011-10-25

next: Semantic Interoperability prev: Segment Tree