I have a somewhat static sorted list of words which I want to save in a database table:

-- simplified!
-- Note: the rank/order of each item is NOT calculable from other values!
CREATE table words (
  word NVARCHAR(50),
  -- foreign key

The order of words in a 'book' have to be retained.

But what is the best way to store the order of all datasets?

I've already read How to design a database for storing a sorted list? on this site. But I don't have many INSERTs (if I will ever have one) between two records and I don't expect my database to grow up to more than 100k.

So I thought I could use the primary key column id for storing the index in the list. But what if I have to insert one record between two ids?

The other possibilty is to add another column. Is it better to store the numerical position of the dataset or the neighbour IDs in there?

I'm using MySQL.


I have this list from an external resource:

  1. house
  2. dog
  3. browser
  4. database

Now I have to enter these values in same order into the database!

INSERT INTO words (word) VALUES ('house'), ('dog'), ('browser'), ('database')

The order is now described via the id column (which is the primary key at the same time).

But suddenly, I have to insert another word between 'house' and 'dog'. I can't simply change the PK id because that would break other table relations.

  • can you use a rank function in a view to return the "id" (rank), this will be computationally intensive, but 100k records is not that many.. Dec 24, 2012 at 17:02
  • @Rohan But I can't really compute the 'rank' itself. It depends on which word the user saves at first.
    – ComFreek
    Dec 24, 2012 at 17:13

3 Answers 3


By all means use an unsigned int surrogate key as your primary, clustered index. However, instead of using sequential values, build some padding into the sequence. This means that you'll have to assign the id manually instead of using auto_increment.

If you use unsigned int in MySQL, the max value is 4,294,967,295. If you expect to have at most 100,000 rows that means you could space each word out by more than 42,000.

When you need to insert a word between two existing words, just plug it into the space half way between. Let's say you use 40,000 as your intitial padding value. If you have "house" at 800,000 and "dog" at 840,000 you can insert "nouveau" at 820,000.

  • Many thanks for your answer! I had the same idea, but there's still one problem resulting from the oversimplification I made: there is another column called for example 'book_id' which is a PK of another table. The problem is now that I have to retain the order inside all words belonging to a single 'book'. I could adopt the same approach with gaps but wouldn't it be easier to just add another column?
    – ComFreek
    Dec 25, 2012 at 16:08
  • @ComFreek - You should keep your foreign key to the other table (book_id) in any case. If your sorting ID needs to be sorted within each book as opposed to overall, then you can still use the gaps approach. Just sort the words according to whatever combination of factors you need before assigning the sorting IDs with the gaps in them.
    – Joel Brown
    Dec 25, 2012 at 16:40
  • So I just use 0-10,000 for book 1, 10,000-20,000 for book 2?
    – ComFreek
    Dec 25, 2012 at 16:46
  • @ComFreek - Sure, that should work fine. It doesn't have to be 10,000 per book. Just sort all of the words for your initial load by book and then by word order and start loading. Each word will be 10,000 (or whatever) above the previous word and each book will start and end where ever it happens to. It doesn't really matter since the word IDs are meaningless except insofar as they are in a particular order.
    – Joel Brown
    Dec 25, 2012 at 17:23

I would use an auto-number col as the primary key.

Add another col for the rank.

When adding a new item to the table set If the rank is last set the rank to be MAX(RANK) +1.

If the rank is in the middle increment the rank of all other items , by the following.

UPDATE Words SET  Rank = Rank +1 where Rank >= CurrentRankReplacedId

I agree, this is something that you can not calculate , and will need to store.

  • Thanks for your answer, but I think you have misunderstood me. I've included an example in the question body which should make things clearer. I also forgot to say that I'm using MySQL.
    – ComFreek
    Dec 25, 2012 at 10:20
  • Apologies ComFreek, hope this is a more appropriate solution. Dec 25, 2012 at 18:26
  • Hi Richard, I've just found another solution combining a rank mechanism and gaps (as Joel said). I'll post a solutino tomorrow ;) +1 for you both BTW.
    – ComFreek
    Dec 25, 2012 at 19:40

Here is my table CREATE statement:

  book_id UNSIGNED INT

How does it work now?

  1. rank specifies the relative rank (position) within the list of all words of one book (i.e. same book_id value)

  2. In order to be able to simply insert one word, the application layer (PHP script, etc.) or a trigger on the database side will provide gaps within two ranks instead of incrementing the old one. That avoids having to change all entries after the insertion rank (position).
    Because the rank is relative to the book, we have a maximum space for 4,294,967,295 ranks. So the rank gap can be choosen very generous.

Thanks to Richard Vivian who pointed me to a rank-based solution, and Joel Brown who mentioned the possibility of gaps between two (rank) values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.