1

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?

2 Answers 2

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.

0

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.

5
  • 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, 2013 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, 2013 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, 2013 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, 2013 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. Aug 28, 2013 at 11:29

Your Answer

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

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