DB design question

Ignition
1

This is a more general database design question than what might be appropriate for this forum, but I thought I’d ask anyway as there’s lots of talented developers here.

I’m trying to implement what I’m calling (for lack of a better term) a change authorization table for our recipe data tables. We have a few master recipe segment data tables that I need to protect from unauthorized changes. We also have a recipe table for each segment table to give the segments some context.

An example of a segment table:

 Segment | TemperatureSP | Minutes | GuaranteedSoak | MaxCooling | DisableBurners | EndOfRecipe
---------+---------------+---------+----------------+------------+----------------+-------------
       1 |          1135 |       3 | t              | f          | f              | f
       2 |          1135 |     117 | f              | f          | f              | f
       3 |          1135 |      90 | f              | f          | f              | f
       4 |           550 |       1 | f              | f          | f              | t
       5 |          1135 |       3 | t              | f          | f              | f
       6 |          1135 |     117 | f              | f          | f              | f
       7 |          1135 |     175 | f              | f          | f              | f
       8 |           550 |       1 | f              | f          | f              | t
       9 |          1135 |       3 | t              | f          | f              | f

And here’s an example of the recipe table that gives the previous one context:

 recipe_number | segment_range |                             description
---------------+---------------+--------------------------------------------------------------
             1 | [1,5)         | 2 hours at 1135, 1:30 hours ramp down to 550
             5 | [5,9)         | 2 hours at 1135, 2:55 hours ramp down to 550
             9 | [9,13)        | 3 hours at 1135, 4:25 hours ramp down to 550
            13 | [13,17)       | 4 hours at 1135, 5:50 hours ramp down to 550
            17 | [17,21)       | 4 hours at 1350, 3:30 hours ramp down to 1000

What I’m considering doing is adding a table to hold authorized changes. Something like the following:

CREATE TABLE change_authorization (
    table_changed text,
    item_id_changed int,
    change_date date,
    person_authorizing_change text,
    change_description text
	);

table_changed would refer to one of the recipe tables, and the item_id would refer to the recipe number being changed. The person and description would be just text describing the change event (this is the record I need to keep and display later).

What I would like to do is write some sort of trigger function that would disallow updates to the segment table, unless the segment belongs to a recipe that has a current (change_date = today) change authorization entry.

Has anyone dealt with a similar situation? Does this seem like a logical approach to recording the change authorizations? Or am I creating a monster here?

2

Hello Brian,

Not really getting the total picture, however here’s a few thoughts on the database design (since that’s what you are asking)

  1. The recipe table seems to have 0 normalization due to the fact that the segment_range attribute holds a range of data (actually a range of foreign key constraints to another table), which in my experience can be a real pain to work with. Instead keep your tables in at least 1. normal form, or do 2. or 3. if possible. The extra work normalizing your tables is well worth it when you need to deal with something like this later. Here’s a link to wiki describing this if not familiar.
  2. Regarding the trigger, maybe this is too much for the database to handle and should be handled by a persistence layer in your application? Maybe everything works out with standard FK constraints when the tables are normalized.

Just my thoughts and as said, not really getting the total picture and I might have misunderstood something.

1 Like
3

Hi Claus,
Thanks for the reply.

I’ll try to give more background. I often have trouble giving enough information about things. It’s clear in my head, but I need to remember that not everyone lives there :sweat_smile:

We have several types of equipment with similar controller styles. The controllers have setpoint schedulers that are segment based. The types of equipment do different things, so the controller segment tables have different content. What I had listed was a segment table for one of the types. I have in ignition a master or reference table of the segment table contents for a controller type. I also have a table for each controller with the exact same layout. Those tables are updated by ignition to reflect the data that is actually in each controller. We sometimes make temporary changes to the segment data in the controller, so having the reference table allows us to easily revert back to the normal segment data when they are finished with the modified segments.

The recipe table is something that gives more context to the segment tables. Using the segment ranges, I can print a recipe report showing the segments that that recipe uses, or draw a chart of the expected setpoint changes for example. I do see what you mean about the non-normal form of the table. When I was designing that table, I wanted to be able to ensure that my recipe segment ranges weren’t able to overlap. In postgresql, the easiest way I know of to do that is to have a integer range column with a constraint that prevents overlapping ranges. The recipe number was how we were originally referring to each recipe, so that was more of a carry-over from the paper indexes. In hindsight, I could probably redesign the application side to just use lower(segment_range) in the cases where I was using recipe_number and get rid of the recipe_number column. I’ll admit that ranges can be a pain to work with at times, but in this case I’d probably rather keep the range and get rid of the integer column.

Now, as to the changes I’m trying to make, we recently decided (in response to an ISO audit recommendation), that we should record some contextual data when someone wants to change something in the reference recipe segment tables. Specifically, we want to record who authorized the change (this is different than who is logged in to ignition. I’m planning on just having a text field for this), why the change was being made, and things like that. I’d also like to make just one common change authorization table that all of the reference recipe segment tables can use (remember, I have several of the reference tables, one for each type of controller). I can also see the change authorization table being useful for other things in the future.

In addition to simply recording the information regarding the change, I’d like to be able to disallow writes to the reference table rows unless there’s a current change authorization entry for that specific table/recipe. I’m pretty sure I can use a trigger function to do this, but I’m a little outside my small box of experience with something like that.

I’ll keep going that direction, but I was hoping someone might have been able to say, “oh, we had that problem and here’s how we handled it”, or “you definitely don’t want to do it that way!”.

Again, thanks for the reply! Hopefully I’ve given enough context to show the big picture. If you have further thoughts or questions, or if anyone else wants to chime in, I’m all ears (or eyes, I guess) :slight_smile:

4

In case anyone happens across this post in the future, I did get something working in the database for this functionality.
I ended up with a change authorization table for each recipe segment table. That allowed me to keep a foreign key constraint on the recipe number in the change authorization table. My trigger function ended up looking like this:

CREATE OR REPLACE FUNCTION check_recipe_change_authorization() RETURNS TRIGGER AS
$BODY$
/*
 * This checks to see if a current change authorization entry exists for the
 * recipe segment being modified.  If not, it raises an exception and cancels the update.
 * This function takes two arguments passed from the trigger statement.  The first is the
 * recipe table that should be checked to find the recipe number for the segment.  The second
 * is the table that should be checked for a change authorization entry.
*/
DECLARE
    recipe_table TEXT  := TG_ARGV[0]::text;
	change_auth_table TEXT := TG_ARGV[1]::text;
	timestamp_of_change timestamptz;
BEGIN
    --check number of arguments is correct
   IF TG_NARGS != 2
   THEN
     RAISE EXCEPTION 'Function check_recipe_change_authorization requires two table arguments to be passed';
   ELSE
	 --get timestamp of most recent change authorization for this recipe
	 EXECUTE format(
	  'SELECT change_timestamp FROM %2$I 
        WHERE recipe_id = (
         SELECT lower(segment_range) FROM %1$I WHERE segment_range @> $1."Segment"
        )
       ORDER BY change_timestamp DESC LIMIT 1',recipe_table, change_auth_table)
	   INTO timestamp_of_change
	   USING NEW;
	 --check to see if the latest timestamp is less than 12 hours old
	 IF now() < (timestamp_of_change + '12 hours'::interval)
	 THEN
	  --valid timestamp found, return NEW to allow the change
	  RETURN NEW;
	 ELSE
	  RAISE EXCEPTION 'No current change authorization entry found for this segment!';
     END IF;
   END IF;	 
END;
$BODY$ LANGUAGE plpgsql;

And the trigger definition itself looked like this:

CREATE TRIGGER testing_change_authorization
BEFORE UPDATE ON testing_master_recipe_segments
FOR EACH ROW EXECUTE PROCEDURE check_recipe_change_authorization('testing_recipes', 'testing_recipe_change_authorization');
1 Like