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.
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.
If you can use XML with a relational database, that's probably your best option. Here are some good links:
'Hope that helps!