Thread: How to ensure a log-entry is created based on state of data in other tables
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 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.
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?
Attachment
[…]
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.
Is there a better way, using some extra tables to do bookkeeping can the 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.
Ah, this is what I'm looking for, I think.
