0

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.

  • Is this really about shoes and jeans and their colours? – dezso Oct 16 '15 at 13:19
  • Well, not really. But a kind of.. – Yata Oct 16 '15 at 13:30
  • color should be an attribute of box – a_horse_with_no_name Mar 15 at 10:27
0

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.

  • Thank you very much for your answer. Unfortunately an item (shirt, jeans or shoes) can also not be added to any box. So the idea to duplicate (or move) the color attribute won't work. As you have pointed out, the real problem is not about boxes and clothes. The real problem is about archaeological finds that have in common only an historical period. This is the reason why, unfortunately, I can't merge jeans, shirts and shoes in a "One True Lookup Table". – Yata Oct 17 '15 at 8:42
  • Right, but the problem you've listed is a normalization problem, whereas the problem you have is potentially something else. We can't guess what that problem might be is unless you give us the actual problem, rather than a toy example. My solution might be that "box" is "historical period" and the "t-shirt" is the "artefact type." Before being related to a historical period, the period is "unknown," or NULL. Maybe that's not the problem at all - but we won't be able to tell until you lift the hood. – Avarkx Oct 19 '15 at 15:49
0

The specific problem of having the trigger consider a group of related changes rather than independant updates could be solved by :

  • declaring the trigger to being a constraint trigger deferred at COMMIT time.

     CREATE CONSTRAINT TRIGGER trigger_name
      AFTER INSERT OR UPDATE ON table_name
      DEFERRABLE INITIALLY DEFERRED
      FOR EACH ROW EXECUTE PROCEDURE check_colors();
    
  • and enclosing the set of changes inside a transaction (BEGIN/COMMIT pair)

     BEGIN;
     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;
     COMMIT;
    

Still, concurrent transactions should be blocked by the program to work on the same "boxes", otherwise a trigger-based check could let incoherencies pass. This is because the trigger can't see the potential changes of other sessions that would not have yet committed when it runs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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