public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andreas Joseph Krogh <[email protected]>
To: [email protected]
Subject: How to ensure a log-entry is created based on state of data in other tables
Date: Thu, 9 Feb 2023 08:23:59 +0100 (CET)
Message-ID: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net> (raw)



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 protected] <mailto:[email protected]>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: How to ensure a log-entry is created based on state of data in other tables
  In-Reply-To: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox