What is proper database design for a drupal table with an order field?

Question

I am adding a custom widget to an instance of Drupal 6.x & MySQL 5.5 and came across a problem with updating rows.

I have a table of recipe ingredients where multiple ingredients are tied to a single recipe by node id (nid) & version id (vid).

The primary key is vid, nid & order.

The vid & nid are related to the recipe nid & vid fields.

The table schema is:

+-----------------+------------------+
| Field           | Type             |
+-----------------+------------------+
| vid             | int(10) unsigned |
| nid             | int(10) unsigned |
| order           | int(10) unsigned |
| name            | varchar(255)     |
| unit_of_measure | varchar(32)      |
| quantity        | int(10) unsigned |
+-----------------+------------------+

The problem comes when attempting to reorder the ingredients.

For instance:

+-----+-----+-------+---------+-------------------+----------+
| vid | nid | order |  name   |  unit_of_measure  | quantity |
|  5  |  1  |   1   | Chicken |        Lb         |    1     |
|  5  |  1  |   2   |  Rice   |        Cup        |    2     |
|  5  |  1  |   3   |  Thyme  |        Tbsp       |    3     |
+-----+-----+-------+---------+-------------------+----------+

I want to move Thyme to the top of the list but I can't change the order for Thyme to 1 since that primay key already exists (Chicken). I can't move Chicken down to order 2 because Rice is already there, etc...

My position is that we should add a unique auto-incrementing int field that will be the sole primary key. Which will enable us to reorder the rows without incident with the possibility that two rows might end up with the same nid, vid, & order.

My coworkers position was that to add a unique auto-increment int field is bad design because there should never be two different rows that have the same vid, nid & order. But following this belief there are two ways to implement a reorder of the rows

1. Update each row's order with some large number (ie- 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).
2. Delete each row that has the same nid & vid, then insert all the rows again in the correct order.

From the database's perspective, what is the correct approach?

Answer 1

The problem is similar to this (SO) question:
How to swap values of two rows in mysql without violating unique constraint?

Unfortunately, the answer is the same. Due to how MySQL checks UNIQUE (and primary and foreign key) constraints, i.e. row by row and not at the end of statements or at the end of transactions, swapping or rearranging values that are part of unique constraints, cannot be done easily.

I would go with your 1st suggestion:

1. Update each row's order with some large number (i.e. 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).

This way you can have the minimal (2) statements required to do the update and any other connection that reads the table between the 2 statements will still read a consistent state of values.

Answer 2

Why not recreating the primary key as (nid, vid), and creating a new index on the column "order" (just for fast retrieval / ORDER BY clause).

--> The worst that can happen is to have 2 ingredients with the same "order" value, but the order-change-logic should be already correctly defined in the application.