What's the best way to implement a rank field in a database?

Question

Suppose you have a table where you store the musical preferences of people, and you'd like to give users the ability to rank their preferences. At the time of insertion, the rank field should be calculated automatically. Later, however, the user should have the ability to edit his rankings.

id, memberid, trackid, rank

EDIT: It appears the best way to do this is to find the get the max for a member's existing ranks and add one...

Answer 1

Make rank auto increment? It will just increment that field everytime you make an insert. Like id.

Rereading your question i think I am misunderstanding it. Do you want rank to be automatic or is it a field that users set depending on subject? if its the former I don't understand whats wrong.

Answer 2

How are your users going to rank the tracks? Will they order them one after the other in some user interface?

If this is the case, I suggest delegating the rank numbering to the client-side (the interface). Therefore if you'd be having an interface that allows users to drag and move tracks around to rank them in order, simply assign them a sequential rank number, and when they hit save, send this information to the database.

---

UPDATE:

If you want a new record to always be ranked last you could simply get the MAX(rank) WHERE memberid = ? and then INSERT the new row with a rank + 1 as you suggested in the question. Wrapping everything in a transaction will guarantee atomicity.

Answer 3

It sounds like you want to rank the newly inserted item automatically at the bottom.

INSERT INTO Prefs (MemberId, TrackId, Rank)
   SELECT   @MemberID, @TrackID,
            MAX(ISNULL(Rank,0))+1
   FROM Prefs
   WHERE MemberId = @MemberID

Aside: Why not keep rank null until the user actually gives each item a rank value? Present the user with all the items, and when they fill in (or not!), the rank, then run the UPDATE statement for each item.

Answer 4

I think the question you're asking is "how do I know what the next rank should be for each user?"...but I'm not sure.

If so, you could simply cheat and keep a "max rank" field in the users table for each userID. You'd query that value before inserting a new ranking and increment it for both the users table and the rankings table.

Alternatively, you could use a "select max(rank) from rankings where memberid = ?" type query to have the database compute it for you. Not the most efficient, but for an iTunes-like DB it would probably be managable (that is, as long as the number of songs * users isn't "too" large).