Page 1 of 1

Unique id + semi unique thread_id column.

Posted: Fri Oct 19, 2007 3:33 am
by arpowers
Hi!

I'm trying to create a messaging system for a project I'm working on.
Theoretically I only need one table to do this, but I do need a unique auto increment primary key 'id' and a thread_id column.

The thread_id column will contain a unique value for new messages, and will contain referential values for thread replies (children)..
What is the best way of accomplishing this or should I just create a thread table with just one column?

So to recap I need standard id, plus a thread_id that will be unique for new threads and referential for replies.

Thanks!

Posted: Fri Oct 19, 2007 4:35 am
by Kieran Huggins
Storing trees in SQL is certainly cool! In this solution you can retrieve an entire thread in just one query! That's hawt.

This is the original article that started the whole thing off (1996):
http://www.dbmsmag.com/9603d06.html

Rails implements it a few ways:
http://wiki.rubyonrails.org/rails/pages/ActsAsNestedSet (comes with Rails)
http://wiki.rubyonrails.org/rails/pages/BetterNestedSet (a plugin)

Here's the overview of what you want:
http://api.rubyonrails.org/classes/Acti ... thods.html

And here's the source code (in Ruby, but you could learn a lot from this, then write it in PHP)
http://api.rubyonrails.org/classes/Acti ... thods.html

There may already be a PHP version of this, but I don't know it.