4 ms·
The closure table is a neat solution, even though the adjacency list is by far the most natural to me. As the author notes, breadcrumbs and subtrees can be gene
by snaily 10y ago
The closure table is a neat solution, even though the adjacency list is by far the most natural to me. As the author notes, breadcrumbs and subtrees can be generated in the database layer by recursive CTEs, and support has become more common since 2010. SQLite has added WITH RECURSIVE, for instance.
- combatentropy 10y agoYes, the first way, the adjacency list, has the simplest table layout and still lets you set foreign keys. I strongly recommend PostgreSQL's WITH RECURSIVE queries to go along with it (https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with....). Or now that I know SQLite has it, I recommend either one over MySQL. Writing such queries is mindbending for a while. But it's worth it to be able to take care of it all in the database. Before, I would write a function in PHP that did a database query, then called itself to do another database query, and so on until it was done.
- dspillett 10y ago> the adjacency list is by far the most natural to me That is why such models are sometimes called "naive trees" - it is the obvious way to fit the minimum requirements (and, of course, sometimes all the requirements) but some types of reporting on the structure are inefficient in the general case (or impossible without advanced query features like CTEs). > subtrees can be generated in the database layer by recursive CTEs Be careful with CTEs in postgres: they are an optimisation fence with regard to predicate pushing so for some query patterns you'll see index scans where you think it might be able to use seeks. This may be the case in other DBs too. It isn't in SQL Server (usually), I don't know about others.