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 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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
We can't use just nid & vid for a primary key, those will be the same for any ingredients in the same recipe –  Patrick May 30 '13 at 11:52
    
The reason my coworker was saying we can't have two ingredients with the same order is that having duplicate order values is bad design –  Patrick May 30 '13 at 11:53
    
well ... you're already using a bad design. Your current tables structure is not fully normalized. <br> How about spiting your 2 tables structure "recipes" & "recipe ingredients" into 3 tables: {recipes[nid, name]}, {ingredients[igredient_id, name]} and {ingredients2recipes[nid,vid,ingredient_id,order]}. And add a PK on ingredients2recipes(nid,vid,ingredient_id). I think that is a correct & logical way to do things anyway. –  MTIhai May 30 '13 at 13:39
    
Unfortunately if you were to delete an ingredient after the fact then there is still the problem of having incorrect ordering, but I believe that is the best way to go –  Patrick May 31 '13 at 20:02
    
@MTIhai I don't see how the design is not fully normalized. The OP has not shown what other tables he has. –  ypercube Aug 28 '13 at 11:29

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.