What database can store trees?

Is there a DB out there that can store tree structures (like for nested comments) without using anti-patterns or such. I think LDAP is one, but are there any others?

I need to be able to index childes as well. I need it to be easy to move a branch from one node to a different node and be fast to read + format.

I have seen other similar questions. The problem with those (for me) they ask what is the most efficient way to do it in db XXX, while I ask which DB I should use.

Answers


Relational databases won't be the best for this if the tree is very deep. I'd recommend a graph or object database.

"Storing comments"? Like those following blogs or articles? If that's the case, I'd say that you can assume they won't be that deep. A ten level comment tree would be exceptional.

In that case, a simple relational parent/child relationship using foreign keys would be sufficient:

CREATE TABLE IF NOT EXISTS article
(
    article_id integer not null auto_increment,
    comment_id integer,
    primary key(article_id),
    constraint fk_comment foreign key(comment_id) references article(article_id) on delete cascade on update cascade
);

"Relational Databases", of course, emphasize "tables" (2-D relationships).

Among RDBMS vendors, there are many different alternatives. For example, DB2 has "hierarchical structures", and Oracle has "hierarchical queries":

More generally, most of the major RDBMS products (MS Sql Server, IBM DB2, Oracle) have all become XML-aware. This provides a more robust, portable approach to manipulating structured data.

Of course, among non-RDBMS vendors, there are even more alternatives for dealing with non-relational data (like trees). Correspondingly, there's less portability - locking yourself into one "non-SQL database" more often than not locks you OUT of easily migrating your application to a different database.

BOTTOM LINE:

If you can use XML with a relational database, that's probably your best option. Here are some good links:

'Hope that helps!


Need Your Help

add close button to ajax fancybox

jquery ajax popup fancybox modal-dialog

I am using fancy box 1.3 with ajax option.

GWT, ASP.NET, jQuery… can you clarify me?

asp.net gwt

Does someone can help me to understand WHERE to collocate Gwt compared to (for exmaple): asp.net or jquery ?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.