Subject: How to ensure a log-entry is created based on state of data in other tables

From: Andreas Joseph Krogh <[email-masked]>
Date: Thu, 9 Feb 2023 08:23:59 +0100 (CET)

Hi.

I'm not sure what the best subject is for this email, but here are the 
requirements: When all activities for a given product has status='DONE' then an 
entry inactivity_product_log should be created. This should be guaranteed 
somehow. If an activity for which there exists an entry inactivity_product_log 
is set to status='NOT_DONE', then the entry inactivity_product_log should be 
deleted.

Here's an example-schema:

DROP TABLE IF EXISTS activity_product_log;
DROP TABLE IF EXISTS activity;
DROP TABLE IF EXISTS product;

CREATE TABLE product
(
id INTEGER primary key,
name varchar not null unique
);

CREATE TABLE activity
(
id serial primary key,
name varchar not null unique,
product_id integer not null references product(id),
status varchar NOT NULL,
CHECK (status IN ('NOT_DONE', 'DONE'))
);

CREATE TABLE activity_product_log
(
id serial primary key,
product_id integer not null references product(id),
created TIMESTAMP NOT NULL
);

INSERT INTO product(id, name) VALUES(1, 'Horse'), (2, 'Fish');

INSERT INTO activity(name, product_id, status)
VALUES ('A1', 1, 'NOT_DONE'), ('A2', 1, 'NOT_DONE'), ('A3', 1, 'NOT_DONE'), (
'A4', 1, 'NOT_DONE')
 , ('A5', 2, 'NOT_DONE'), ('A6', 2, 'NOT_DONE')
;

UPDATE activity SET status = 'DONE' WHERE name = 'A1';
UPDATE activity SET status = 'DONE' WHERE name = 'A2';
UPDATE activity SET status = 'DONE' WHERE name = 'A3';
UPDATE activity SET status = 'DONE' WHERE name = 'A4'; -- Here an entry in 
activity_log should be created
I'm thinking about using constraint-triggers for this but will that actually 
guarantee the requirements? I'm worried about the last part of the requirement, 
if all activities for a product are ‘DONE’ but then gets toggled ‘NOT_DONE’ and 
‘DONE’ “very fast”.

Is there a better way, using some extra tables to do bookkeeping can the have 
constraints ensuring this business-requirement?

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email-masked] <mailto:[email-masked]>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

---

From: Steve Midgley <[email-masked]>
Date: Thu, 9 Feb 2023 07:08:16 -0800

On Wed, Feb 8, 2023 at 11:24 PM Andreas Joseph Krogh <[email-masked]>
wrote:

> requirements: When all activities for a given product has status='DONE'
> then an entry in activity_product_log should be created. This should be
> guaranteed somehow. If an activity for which there exists an entry in
> activity_product_log is set to status='NOT_DONE', then the entry in
> activity_product_log should be deleted.
>
>
>
> *DROP TABLE IF EXISTS **activity_product_log*;
> *DROP TABLE IF EXISTS **activity*;
> *DROP TABLE IF EXISTS **product*;
>
> *CREATE TABLE **product*
> (
>     id *INTEGER primary key*,
>     *name **varchar not null unique*
> );
>
> *CREATE TABLE **activity*
> (
>     id *serial primary key*,
>     *name **varchar not null unique*,
>     product_id *integer not null references **product*(id),
>     status *varchar NOT NULL*,
>     *CHECK *(status *IN *(*'NOT_DONE'*, *'DONE'*))
> );
>
> *CREATE TABLE **activity_product_log*
> (
>     id *serial primary key*,
>     product_id *integer not null references **product*(id),
>     created *TIMESTAMP NOT NULL*
> );
>
> *INSERT INTO **product*(id, *name*) *VALUES*(1, *'Horse'*), (2, *'Fish'*);
>
> *INSERT INTO **activity*(*name*, product_id, status)
> *VALUES *(*'A1'*, 1, *'NOT_DONE'*), (*'A2'*, 1, *'NOT_DONE'*), (*'A3'*, 1, *'NOT_DONE'*), (*'A4'*, 1, *'NOT_DONE'*)
>      , (*'A5'*, 2, *'NOT_DONE'*), (*'A6'*, 2, *'NOT_DONE'*)
> ;
>
> *UPDATE **activity **SET *status = *'DONE' **WHERE **name *= *'A1'*;
> *UPDATE **activity **SET *status = *'DONE' **WHERE **name *= *'A2'*;
> *UPDATE **activity **SET *status = *'DONE' **WHERE **name *= *'A3'*;
> *UPDATE **activity **SET *status = *'DONE' **WHERE **name *= *'A4'*; *-- Here an entry in activity_log should be created*
>
> I'm thinking about using constraint-triggers for this but will that
> actually *guarantee* the requirements? I'm worried about the last part of
> the requirement, if all activities for a product are ‘DONE’ but then gets
> toggled ‘NOT_DONE’ and ‘DONE’ “very fast”.
>
>
>
> Is there a better way, using some extra tables to do bookkeeping can the
> have constraints ensuring this business-requirement?
>
>
>
What is the time window required for "DONE" and "NOT_DONE" to be correct?
Do they need to be atomic (meaning the time window is effectively 0)? Or
can the system "notice" recent changes and keep track of done/not done
after-the-fact? If your time window is > 0, it seems like recurring
processes could be set up to track DONE / NOT_DONE?

Another totally different way to think about this is to create a view that
provides answers on DONE and NOT_DONE as computed values based on the
underlying state of the table at the time the view is queried? That would
seem to satisfy a time window of 0?

Steve

---

From: Andreas Joseph Krogh <[email-masked]>
Date: Thu, 9 Feb 2023 17:33:07 +0100 (CET)

På torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
[email-masked] <mailto:[email-masked]>>:

[…]

What is the time window required for "DONE" and "NOT_DONE" to be correct? Do 
they need to be atomic (meaning the time window is effectively 0)? Or can the 
system "notice" recent changes and keep track of done/not done after-the-fact? 
If your time window is > 0, it seems like recurring processes could be set up 
to track DONE / NOT_DONE?

Another totally different way to think about this is to create a view that 
provides answers on DONE and NOT_DONE as computed values based on the 
underlying state of the table at the time the view is queried? That would seem 
to satisfy a time window of 0?

Steve 
Yes, they need to be atomic. Either all are DONE and there is an entry in 
activity_product_log for product_id, or there is no entry in 
activity_product_log.

---

From: Steve Midgley <[email-masked]>
Date: Thu, 9 Feb 2023 08:37:35 -0800

On Thu, Feb 9, 2023 at 8:33 AM Andreas Joseph Krogh <[email-masked]>
wrote:

> [email-masked]>:
> Yes, they need to be atomic. Either all are DONE and there *is* an entry
> in activity_product_log for product_id, *or* there is no entry in
> activity_product_log.
>
>
>
So, would the view table approach work? So DONE / NOT_DONE is simply
calculated at the time that view is queried? It seems atomic to me,
especially if the query to the table is made with the appropriate
concurrency flags?

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 9 Feb 2023 09:44:25 -0700

> have constraints ensuring this business-requirement?
Every time an activity newly becomes "Not Done" increment a "not done seen
counter" and likewise with a "done seen counter".  Whenever the former is
greater than the later you have one or more not done activities.

David J.

---

From: Rob Sargent <[email-masked]>
Date: Thu, 9 Feb 2023 09:44:38 -0700

On 2/9/23 00:23, Andreas Joseph Krogh wrote:
> requirements: When all activities for a given product has 
> status='DONE' then an entry in |activity_product_log| should be 
> created. This should be guaranteed somehow. If an activity for which 
> there exists an entry in |activity_product_log| is set to 
> status='NOT_DONE', then the entry in |activity_product_log| should be 
> deleted.
>
>      *CHECK *(status *IN *(*'NOT_DONE'*,*'DONE'*))
> );
>
> *INSERT INTO **product*(id,*name*)*VALUES*(1,*'Horse'*), (2,*'Fish'*);
>
> *INSERT INTO **activity*(*name*,product_id,status)
> *VALUES *(*'A1'*,1,*'NOT_DONE'*), (*'A2'*,1,*'NOT_DONE'*), (*'A3'*,1,*'NOT_DONE'*), (*'A4'*,1,*'NOT_DONE'*)
>       , (*'A5'*,2,*'NOT_DONE'*), (*'A6'*,2,*'NOT_DONE'*)
> ;
>
> *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A1'*;
> *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A2'*;
> *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A3'*;
> *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A4'*;/-- Here an entry in activity_log should be created/
>
> actually /guarantee/ the requirements? I'm worried about the last part 
> of the requirement, if all activities for a product are ‘DONE’ but 
> then gets toggled ‘NOT_DONE’ and ‘DONE’ “very fast”.
>
> Is there a better way, using some extra tables to do bookkeeping can 
> the have constraints ensuring this business-requirement?
>

Is there any ordering or workflow associated?
Does the product (or perhaps product type) define number of activities 
(or list of "activity.name"s).  Is there no "activity type"?
Can a product be "DONE" more than once?  If not perhaps the product 
table should have the status field?

---

From: Andreas Joseph Krogh <[email-masked]>
Date: Thu, 9 Feb 2023 17:59:03 +0100 (CET)

På torsdag 09. februar 2023 kl. 17:44:25, skrev David G. Johnston <
[email-masked] <mailto:[email-masked]>>:

On Thu, Feb 9, 2023 at 12:24 AM Andreas Joseph Krogh <[email-masked] 
<mailto:[email-masked]>> wrote:

David J.

Ah, this is what I'm looking for, I think.