How to Insert row between two rows and give it priority in database ?

Question

Hi everyone!.

i have a stack of messages in database table. i want to send these messages by their priority so i added "Priority" column to "messages" table.

but what if i want to insert "cram" message between two messages and give the previous priority to this new message?.

Should i update all messages priority under this message.

So please give me the perfect design for my database table to support priority update.

Answer 1

Just include a timestamp column with the default getdate() value. This way, when sending messages, order by priority asc, createtime desc.

If you don't always want to do Last-In-First-Out (LIFO), you can do order by priority, senddate and then set senddate to 1/1/1900 for anything you want pushed out first.

If you want to do it by some method of ranking them, you'd have to update every single row below a given priority if you wanted to "cram" a message in. With a getdate() default column, you just don't have to worry about that.

Answer 2

Use a float column for Priority rather than an int.

Then, to insert a message between two others, assign the average of the two messages' Priority values as the new message's Priority. (E.g., to insert a cram message between a messages with Priority 2 and 3, assign it a Priority of 2.5).

By doing this, you don't have to update any other messages' priority, and you can continue to average/insert between those, etc. until you bump up against the decimal accuracy limits of a float (which will take awhile, especially if the raw Priority values tend to be small).

Or, add another column after Priority in the ORDER BY. In the simplest case, use bit column called "ShowAfter" with a default value of 0. When you insert a cram message, give it the same Priority as the message you want to see it after, but a [ShowAfter] value of 1.

Answer 3

Interesting, maybe you could use an identity column as the primary key but use an increment that skips a few values?

This way, you would reserve space should you need to insert/update a messages priority to be between an existing boundary.

Make sense?

Answer 4

Just wild idea, haven't test this for performance, but link list kind of structure should net you want you want here. At maximum you will only need to change 3 records

Find out where you want to put your new record, note what record comes before it and what record comes after it. new record, establish previous record and the next record. relink the previous and next records accordingly to the new record.

You do this by adding 2 fields (next and previous) in the schema.

Answer 5

Similar to @Jimmy Chandra's idea, but use a single link column.

So, you might have these columns:

ID | SortAfterID | OtherColumn1 | OtherColumn2

Now, let's say you have five records with IDs 1 through 5, and you want record 5 to be sorted between 2 and 3. Your table would look something like this:

ID | SortAfterID | OtherColumn1 | OtherColumn2
1  | NULL        | ...          | ...
2  | 1           | ...          | ...
3  | 5           | ...          | ...
4  | 3           | ...          | ...
5  | 2           | ...          | ...

I would set a constraint so that SortAfterID references ID.

If you now wanted to insert a new record (ID = 6) that goes between 1 and 2, you would:

1. Insert a new record with ID = 6 and SortAfterID = 1.
2. Update record with ID = 2 so that SortAfterID = 6.

I think this should be pretty low maintenance, and it's guaranteed to work no matter how many times you cram. The floating point number idea by @richardtallent should work as well, but as he mentioned, you could run into a limit.

EDIT

I just noticed the paragraph at the end of @richardtallent answer that mentions this same idea, but since I typed this all out, I figure I'll keep it here since it provides a little extra detail.