Hierarchical Tagging in SQL

Hierarchical tagging in SQL

Ali's answer has a link to Joe Celko's Trees and Hierarchies in SQL for Smarties, which confirms my suspicion - there isn't a simple database structure that offers the best of all worlds. The best for my purpose seems to be the "Frequent Insertion Tree" detailed in this book, which is like the "Nested Set Model" of Ali's link, but with non-consecutive indexing. This allows O(1) insertion (a la unstructured BASIC line numbering), with occasional index reorganisation as and when needed.

Best way to store hierarchical tags

The naive approach would be a parent / child solution, but it's very difficult to write efficient queries with this data model.

Managing Hierarchical Data in MySQL is a pretty good article about hierarchical data structures. I suppose most of it can be applied to other database systems, too.

Recommended SQL database design for tags or tagging

Three tables (one for storing all items, one for all tags, and one for the relation between the two), properly indexed, with foreign keys set running on a proper database, should work well and scale properly.

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

SQL - How to store and navigate hierarchies?

The definitive pieces on this subject have been written by Joe Celko, and he has worked a number of them into a book called Joe Celko's Trees and Hierarchies in SQL for Smarties.

He favours a technique called directed graphs. An introduction to his work on this subject can be found here

---

Related Topics