Reading delicious/popular has become quite a habit for me. It is often a good place to find interesting and obscure programming tricks. The down side is that there is a lot of web-dev stuff in there that really doesn't interest me too much. Even so, the latest and greatest color picker is no where near as evil as this article by a Rock Star coder about storing tree structures in relational databases. Rather than going on a long rant , i'll try to keep this simple:

STORE DATA IN YOUR DATABASE IN A FORMAT THAT YOUR DATABASE UNDERSTANDS.

OK. Maybe I'll flesh that out a lil' more.

People much smarter than you or me write databases. And amongst those people lurk a hardcore group who write query optimizers. Their job is to understand your SQL and find the fastest execution plan given available indices and related statistics. And to do this they tend to rely on the fact that an INTEGER column is used to store INTEGERS and that all the various rules of mathematics apply. Now, unles your database supports the INTEGERS-SEPERATED-BY-PERIODS-REPRESENTING-AN-ORDERED-PATH data type, then you are SOL if you want any help from the query optimizer.

(Of course, if you are using PostgreSQL, then you will find an integer array datatype which supports ordered lists of integers. And the query optimizer knows how to use properly constructed indices to find stuff.)

According to Rock Star, the standard way of doing things fails because "It involves recursion, which is slow and complex." Hmm. Maybe people who find recursion complex shouldn't be re-inventing the wheel? And slow, maybe if you told us more about your application, we could help you work out why a perfectly normal solution doesn't work for you. Rocky then names two more problems with using MySQL's susggested Nested Set Model. OK. Fine, that model does suck. Even so, the only complaint he has listed against the standard 'Adjaceny List Model' is that it uses recursion.

I guess I knew his proposal would suck when one of his requirements was that "Selection of items needs to be possible with SQL.". Dude. You are using a database, so SQL _should_ work. You are just entering a world of pain by totally ignoring the role of the query optimizer, which will have no idea what to do with your janky path representation. And even if thats not a concernt, you have made it super difficult for any DBA to work with this schema as you'll end up with bastardized SQL to do anything apart form your original purpose. If you are 100% sure that your requirements will never change, fine. But what happens when one of your PHP scripts screws up and you have to manually correct the data? What about referential integrity? What do you do when your LIKE's are not of the form '1.2.3%' - e.g., find me all Speakers? Using your format, that turns into a SELECT * FROM junk WHERE parent LIKE '%.2.%'. Ugh. Throw out any misconceptions you may have about LIKE being fast.

In short, if you have exhausted the benefits that you get from using a database as a database you have 2 options:

1. Stop using a relational database if you don't want any of the benefits of a relational database.
2. Still use a relational database, but store your base level data properly. And then create materialized views (or similar) with optimized views for particular use cases.

Known Joshua Reichs:

1. Me
2. My preferred alternate [Congratulations!]
3. New Zealand Josh
4. Arizonan Preacher
5. Christian Graphic Designer [Crazy Skillz!]
6. The Dead One [RIP]