Talks

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

Lorenzo Alberton

Lorenzo Alberton Lorenzo has been working with large enterprise UK companies for the past 10+ years and is currently CTO at DataSift. He's an international conference speaker and a long-time contributor to many open source projects. Lorenzo Alberton's profile on GitHub Lorenzo Alberton's profile on LinkedIN View Lorenzo Alberton's profile on PHP PEAR
View Lorenzo Alberton's Twitter stream Lorenzo Alberton - Sun Certified MySQL 5 Developer PHP5 ZCE - Zend Certified Engineer

Tags

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework