What database can store trees?

Question

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.

Don't most document databases cater for this? Alternatively, look at graph databases — Oded, Dec 17 '11 at 22:25
@Oded - Today I researched MongoDB, it appears it does not http://stackoverflow.com/questions/8547886/can-a-mongodb-collection-have-inside-it-another-collection I would assume others to be the same, if you know one that does, please post its name. — Itay Moav -Malimovka, Dec 17 '11 at 22:26
Look at RavenDB and Neo4J. I am not sure what "without using anti-patterns or such" means - it is very abstract. — Oded, Dec 17 '11 at 22:27
You almost certainly want to store the data itself as XML. Then the question becomes "what's the best way to store, retrieve and manipulate the XML"? — paulsm4, Dec 17 '11 at 23:58

score 0 · Accepted Answer · answered Dec 17 '11 at 22:37

0

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
);

answered Dec 17 '11 at 22:37

duffymo

305,152
44
369
561

:-D check it http://www.tapuz.co.il/Forums2008/ViewMsg.aspx?ForumId=308&MessageId=157872690 Wish it was restricted "just" to 10 This is just one main post out of many... – Itay Moav -Malimovka Dec 17 '11 at 22:39
Do you know that a relational database can't work? Try it until it doesn't, then switch to something like a graph database. XML is hierarchical, too. – duffymo Dec 18 '11 at 00:04
Have you added indexes? Profiled the code? EXPLAIN PLAN? Do you know the root cause of "slow"? – duffymo Dec 18 '11 at 02:30
Got data to support that? Have you profiled the app? Looked at memory? I'll be surprised if you say "yes". – duffymo Dec 18 '11 at 13:37
well, not memory, but I had "hard-coded" the trees of a single page with about 200 posts, and the difference is noticeable. By hard coding I mean, I generated the trees, and then caching only that output (apc). – Itay Moav -Malimovka Dec 18 '11 at 13:53
Still subjective - do you know that the time is spent in querying, or something else in your code? You don't know - that's my point. – duffymo Dec 18 '11 at 13:59
I would say relative and not subjective, but I see your point. One thing Intuitively for me though, It seems such development will be faster and easier to maintain on a linked list DB (Graph DB, I believe). – Itay Moav -Malimovka Dec 18 '11 at 15:08

score 0 · Answer 2 · answered Dec 17 '11 at 22:39

"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!

Relational tables hold sets of n-dimensional points (SQL tables hold bags) & represent n-ary relation(ship)s/associations. (A "relation(ship)" in the sense of FK or FK constraint as misused by pseudo-relational & pesudo-ERM presentations does have an associated 2-D relationship on subtuples of the FK & referenced subrows.) (Ironically the most basic form of tree can be characterized as a binary relation on nodes.) — philipxy, Oct 21 '20 at 03:28

What database can store trees?

2 Answers2