Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  word NVARCHAR(50),
  -- foreign key
  book_id INTEGER UNSIGNED
);

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.


Example:

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.

share|improve this question
    
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.. –  Rohan Dec 24 '12 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 '12 at 17:13

3 Answers 3

up vote 0 down vote accepted

Here is my table CREATE statement:

CREATE TABLE words (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  word NVARCHAR(50) NOT NULL,
  rank INTEGER UNSIGNED NOT NULL,
  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.

share|improve this answer

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.

share|improve this answer
    
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 '12 at 10:20
    
Apologies ComFreek, hope this is a more appropriate solution. –  Richard Vivian Dec 25 '12 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 '12 at 19:40

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.

share|improve this answer
    
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 '12 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 '12 at 16:40
    
So I just use 0-10,000 for book 1, 10,000-20,000 for book 2? –  ComFreek Dec 25 '12 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 '12 at 17:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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