Sequence database field and query for updating sort order

Question

I would like to enable users of my application to define a number of (say) email addresses. Then I would like to give them the ability to rearrange these addresses, so the primary address is on top, secondary next, etc.

Let's suppose I have a UserEmailAddresses table in a database that links a user (UserId) and an email address (EmailAddressId).

1) What data type (int, float, etc) should I use for the field that will hold the sequence in which these email addresses will be sorted by?

2) What query would be efficient at changing these sequence numbers when the user rearranges their positions? Must it modify multiple records?

(I'm using C# and Linq, but psuedo-code welcome).

Answer 1

You will need to add an integer field that stores the sort order of the email addresses.

As new email addresses are added they get assigned the next sort order id. If an address needs to be sorted above an existing address I would use an update statement to update all email addresses with sort order ids greater than the desired sort order index then update the reordered email address to have the desired sort order index.

Table Schema
-----------------------
EmailID INT primary Key //Auto increment
EmailAddress Varchar(N)
SortOrderIdx INT //This would control your display order
UserID INT //This would be the owner of this particular email list

Email Create Statement
-----------------------
SELECT @NewSortOrdrIdx = MAX(SortOrderIdx)+1
FROM EmailTable 
WHERE UserId = @UserID

INSERT INTO EmailTable (EmailAddress, SortOrderIdx, UserID)
VALUES (@EmailAddress, @NewSortOrdrIdx, @UserID)

Email Reorder Statement
-----------------------
UPDATE EmailTable
SET SortOrderIdx = SortOrderIdx + 1
WHERE SortOrderIdx >= @desired_Sort_Order_Idx AND UserID = @UserID

UPDATE EmailTable
SET SortOrderIdx = @desired_Sort_Order_Idx
WHERE EmailID = @resorted_Email_ID AND UserID = @UserID

Email Select Statement
-----------------------
SELECT EmailAddress 
FROM EmailTable
WHERE UserID = @UserID
ORDER BY SortOrderIdx ASC

Answer 2

Here is a simple design, that simplifies handling "collisions". You only need to UPDATE one row for this method to work:

UserEmailAddresses Table
------------------------
YourPKHere        <whatever you have, identity?>
UserId            <whatever you have>
EmailAddressId    <whatever you have>
DisplaySeq        INT
LastChgDate       datetime


SELECT * FROM UserEmailAddresses ORDER BY DisplaySeq ASC, LastChgDate DESC

EDIT sample code

DECLARE @UserEmailAddresses table
(
     YourPKHere        int identity(1,1) primary key
    ,UserId            int
    ,EmailAddressId    varchar(100)
    ,DisplaySeq        INT
    ,LastChgDate       datetime
)

--existing data
INSERT INTO @UserEmailAddresses values (1,'[email protected]',1,'1/1/2009')
INSERT INTO @UserEmailAddresses values (1,'[email protected]',2,'2/2/2009')
INSERT INTO @UserEmailAddresses values (1,'[email protected]',3,'3/3/2009')
INSERT INTO @UserEmailAddresses values (2,'[email protected]',1,'1/1/2009')
INSERT INTO @UserEmailAddresses values (2,'[email protected]',2,'2/2/2009')

--application updates one row, no locking or blocking
update @UserEmailAddresses set DisplaySeq=1,LastChgDate=getdate() where UserId=1 and EmailAddressId='[email protected]' --could say WHERE YourPKHere=n, but you don't give your complete table schema


--display the emails in proper order, with displayable continuous row numbers
SELECT 
    *, ROW_NUMBER() over(partition by UserId order by DisplaySeq ASC,LastChgDate DESC) AS ActualDuisplaySeq
    FROM @UserEmailAddresses
    WHERE UserId=1

--display the e-mails in proper order
SELECT * FROM @UserEmailAddresses Where UserId=1 ORDER BY DisplaySeq ASC, LastChgDate DESC