Trees in the Database: Advanced Data Structures
Despite the NoSQL movement trying to flag traditional databases as a dying breed, the RDBMS keeps evolving and adding new powerful weapons to its arsenal. In this talk we'll explore Common Table Expressions (SQL-99) and how SQL handles recursion, breaking the bi-dimensional barriers and paving the way to more complex data structures like trees and graphs, and how we can replicate features from social networks and recommendation systems. We'll also have a look at window functions (SQL:2003) and the advanced reporting features they make finally possible.
The first part of this talk will cover several different techniques to model a tree data structure into a relational database: parent-child (adjacency list) model, materialized path, nested sets, nested intervals, hybrid models, Common Table Expressions. Then we'll move one step forward and see how we can model a more complex data structure, i.e. a graph, with concrete examples from today's websites. Starting from real-world examples of social networks' and recommendation systems' features, and with the help of some graph theory, this talk will explain how to represent and traverse a graph in the database. Finally, we will take a look at Window Functions and how they can be useful for data analytics and simple inline aggregations, among other things.
All the examples have been tested on PostgreSQL >= 8.4
has been working with large enterprise UK companies
for the past years and is now CTO at DataSift
He's an international conference speaker
and a long-time contributor
to many open source projects
, Book Review
, Cheat Sheet
, Data structures
, Firebird SQL
, Message Queues
, SQL Server
, Zend Framework