Postgresql – Elements inside a container: How to perform atomic operations on them

database-designpostgresqltransactiontrigger

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes.

  1. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
  2. Each element of the box MUST have the same color of the other.

enter image description here

To check (and also to ensure) the last condition I use a trigger.

/*
If I try to INSERT (OR UPDATE) an item in a box
containing elements of different color, it raises an exception.
*/
BEGIN
   IF  (
         SELECT color FROM tshirt WHERE tshirt.box_id = NEW.box_id
         UNION SELECT color FROM jeans WHERE jeans.box_id = NEW.box_id
         UNION SELECT color FROM shoes WHERE shoes.box_id = NEW.box_id
       ) <> NEW.color THEN

          RAISE EXCEPTION 'Error..';
          RETURN NULL;

    END IF;
END;

Now, let's suppose I want to change the color from 'Blue' to 'Red' for any item inside a Box (let's say box_id = 1):

UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
UPDATE Shoes SET color = 'Red' WHERE box_id = 1;

This code will fail because of the trigger.

The only way I can change the color of each item in the box is:

  1. Remove all items from the box (setting their box_id to null).
  2. Change the color of all the elements.
  3. Put all the elements inside the box (setting back their box_id).

Is there any way (through modeling or triggers) to avoid this tricky situation? It would be great if I could change the elements' color atomically.

Thank in advance and sorry for my bad english.

Best Answer

I'd suggest adjusting your model and doing away with the trigger altogether. While the example is somewhat mind-bending, your data model contains a duplicated dependency which serves as the root of your update anomaly problem.

T-shirts ( T ), Jeans ( J ) and Shoes ( S ) can be placed inside a Box ( B ) and they can have a Color ( C ).

Each Element ( E ) of the Box ( B ) MUST have the same Color ( C ) of the other.

Okay, so T → B, T → C, B → C. That is, if we know the T-shirt, we know the Box, if we know the T-shirt, we know the Color, and if we know the Box, we know the Color. Since Color is dependent on Box and Box is dependent on T-shirt, while perhaps not particularly intuitive, Color cannot be directly dependent on T-shirt since it is already dependent through a transitive dependency. That is to say, if we know the T-shirt, we cannot know the Color ( the Color is NULL ), since we must first know the Box.

The existing trigger is actually attempting to compensate for a potential update anomaly a 3NF+ data model would mitigate. If the color of a T-shirt is known at creation time, such a T-shirt could ( and likely should ) be immediately added to a box of the same color. In this way, the need to change the color of all items in a box is performed by merely updating the color attribute of the box itself.

Also, unless there's good reasons to split T-shirt, Jeans and Shoes into their own tables, it might be very much preferable to just consolidate those to an Element table with and ElementType lookup attribute, but much like @dezso, I suspect this is not even kind of really about t-shirt, jean and shoe colors as they are arranged in various boxes.

Related Question