Database design: INSERT ROW BETWEEN 2 ROWS 1

[rexhunter]

I've created a discussion forum. One of the features is that users can react on other users: a subthread.
Example:

Reaction Record number
==================== =============
ReactionA Gates 1
ReactionB Bush 2
ReactionX on Bush 3
ReactionY on Bush 4
ReactionZ on reactionY. 5
ReactionC 6

When I query these records, it is sort by a column called: number. I wrote the numbers behind the example records. The problem: when a user gives a reaction on another, the record has to be for example between number 3 and 4. But the number 3.5 doesn't exists. My application renumbers the whole thing: every record gets the right number. But when the table gets bigger I get a big overhead on the CPU.
Is there any other possibility or idea that professionals database designers user?

 

[SQLSister]

YOu need to read up opn hierarchial structures and the best way to design them. Here's an FAQ to get you started.
How do I do ___________ with a Hierarchical structure?
faq183-5322

Questions about posting. See faq183-874

 

[willir]

Great starter SQLSister. Star for you on identifying the problem. I loved the "Zen on recursion".

Rex

One way to handle this, as with you is my boss in an employee table is to...
Track the (recursive) parent record
Apply a sort order for each thread

tblPost
PostID - primary key
ParentPostID - foreign key to tblPost.PostID
PostDateTime - used to track when the post was created to apply a sort order. This should be generated when the user starts creating the post.