Article Link.

Can you think of another way to do something similar to this? (That is, display a threaded forum with a non-recursive call to the db?) It invovles using an alphanumeric primary key... I'll letcha work on it... :-)

You append to the right of the key with each succeeding level. First post is 01, it's child is 0101, the next level is 010101 and so on. SQL Server sorts them perfectly by default You have to hard code the number of digits that are important though. Ninety-nine is probably enough in most cases. Why don't you write it up as an alternative and I'll post it :)

99 is appropriate if you have a wussy forum. Why not use something like ten digits, and allow A-Z as the unique characters... then you can have 26^10 unique posts per level... much better. :-)

You wrote that query time has to be optimized, but this isn't necessarily crucial. If you're willing to dynamically generate the pages using the sp_makewebtask function when a new posting is made then requesting those pages involves no database queries at all.

The downside is that you need separate physical html/asp pages to store the threads, but it can be generated from a template so it's not too bad.

Rather than using A-Z, choose a set of 64 characters and use base 64 encoding. More efficient than 26 on two levels: If you are using some external language to generate the IDs, you can do it with bit-shifting instead of integer divide and modulo. And, of course, you need even fewer "digits" to accomplish the same result.

However...

Isn't it true that most DBs aren't going to let you create an index on a text field longer than 255 characters? (And you certainly want this field to be indexed, don't you? For the ORDER BY to be reasonably efficient?) If so, then the limit you run into is the number of "levels" of response allowed. Assume we use 4 base-64 "digits" for a message number. Then the limit becomes 63 response levels, right? Of course, even 3 base-64 digits allows message numbers up to 262143, which should be enough for most any message board, and bumps the number of response levels up to 85.

Ehhh...if you can't get along with a message board that allows up to 85 levels of response and 256K messages, then maybe you need to find yet another scheme?

I'm using this scheme in an ASP-based board, and even using Access (for prototyping! don't jump on me!) it works really well.

(Okay, I admit to using 4 base-64 digits...don't ask me why.)

Incidentally, didn't mean to be anonymous. Bill Wilkinson of ChiliSoft...errr...of Cobalt Networks....err, of Sun, I guess, next month. billw@chilisoft.com for now, at least.

graz wrote:

"You append to the right of the key with each succeeding level. First post is 01, it's child is 0101, the next level is 010101 and so on."

Almost. But what you append is the message number, not the level.

Much more efficient. So message 33 replying to message 17 replying to message 4 becomes:

0004
0004-0017
0004-0017-0033

(Hyphens not needed...I use them for clarity while developing and remove them in final code.)

And, as noted, that sorts perfectly.

Bill Wilkinson
billw@chilisoft.com

So, the rule might be: Anytime a proc needs exclusive data, wrap it in an explicit transaction...
Did I get it?
- wtkwest

Can this system be used if you need to be able to view threads that began inside a certain date range?

A recursive system lets the first call dictate the post time filtering reguardless of when replies where made. I don't see how the pre-sorting system can accomplish this.

-Kevin

Much more efficient. So message 33 replying to message 17 replying to message 4 becomes:

0004
0004-0017
0004-0017-0033

(Hyphens not needed...I use them for clarity while developing and remove them in final code.)


is there a way to format a number to be exactly 4 characters long? say my ID value is 32, i want it to read 0032.

ayan

You could do something like this

Answering Kevin's question re post-time filtering.

In my design, I *also* kept a table of threads. Any posting to a thread updated the "last posted to" date in that table. Presto, searches by date are easy. A simple join and you're done.

The separate table for threads has several other uses and makes a lot of other things easier to do.

I guess you could do this with a single table. Just a really messy query involving joining the main table to itself. Hmmm...actually, it wouldn't be all that messy if you added a "threadID" field (which could actually just be the same as the message ID of the first message in the thread).

SELECT m1.* FROM messages AS m1
WHERE m1.threadID IN (
      SELECT DISTINCT m2.threadID FROM messages AS m2 WHERE postingTime BETWEEN xxx AND yyy
) ORDER BY messageID

Doesn't that do it? Still, that's almost surely slower than maintaining the separate threads table, isn't it? Just because of the need for the DISTINCT because of the multiple hits?

Bill Wilkinson

Is there a way to display the number of submessages for each thread?

// Mikael E.

Hi

Is there any good way of doing this without SP ? (Due to cranky ISP)



Flea

Why do u use stored procedures when isnt necessary???
I think u can do the same without the fields of order and level.
With that stored procedure u are making many consults to de database and i think isnt necessary, but maybe u have a good reason to do it and i want to know it.
Thanks