public inbox for [email protected]
help / color / mirror / Atom feedHow to ensure a log-entry is created based on state of data in other tables
7+ messages / 4 participants
[nested] [flat]
* How to ensure a log-entry is created based on state of data in other tables
@ 2023-02-09 07:23 Andreas Joseph Krogh <[email protected]>
2023-02-09 15:08 ` Re: How to ensure a log-entry is created based on state of data in other tables Steve Midgley <[email protected]>
2023-02-09 16:44 ` Re: How to ensure a log-entry is created based on state of data in other tables David G. Johnston <[email protected]>
2023-02-09 16:44 ` Re: How to ensure a log-entry is created based on state of data in other tables Rob Sargent <[email protected]>
0 siblings, 3 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2023-02-09 07:23 UTC (permalink / raw)
To: [email protected]
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
@ 2023-02-09 15:08 ` Steve Midgley <[email protected]>
2023-02-09 16:33 ` Re: How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2 siblings, 1 reply; 7+ messages in thread
From: Steve Midgley @ 2023-02-09 15:08 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected]
On Wed, Feb 8, 2023 at 11:24 PM Andreas Joseph Krogh <[email protected]>
wrote:
> 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?
>
>
>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2023-02-09 15:08 ` Re: How to ensure a log-entry is created based on state of data in other tables Steve Midgley <[email protected]>
@ 2023-02-09 16:33 ` Andreas Joseph Krogh <[email protected]>
2023-02-09 16:37 ` Re: How to ensure a log-entry is created based on state of data in other tables Steve Midgley <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Joseph Krogh @ 2023-02-09 16:33 UTC (permalink / raw)
To: Steve Midgley <[email protected]>; +Cc: [email protected]
På torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
[email protected] <mailto:[email protected]>>:
[…]
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.
--
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2023-02-09 15:08 ` Re: How to ensure a log-entry is created based on state of data in other tables Steve Midgley <[email protected]>
2023-02-09 16:33 ` Re: How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
@ 2023-02-09 16:37 ` Steve Midgley <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Steve Midgley @ 2023-02-09 16:37 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected]
On Thu, Feb 9, 2023 at 8:33 AM Andreas Joseph Krogh <[email protected]>
wrote:
> På torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
> [email protected]>:
>
> […]
>>
>>
>>
> 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.
>
>
>
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
@ 2023-02-09 16:44 ` David G. Johnston <[email protected]>
2023-02-09 16:59 ` Re: How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2023-02-09 16:44 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected]
On Thu, Feb 9, 2023 at 12:24 AM Andreas Joseph Krogh <[email protected]>
wrote:
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2023-02-09 16:44 ` Re: How to ensure a log-entry is created based on state of data in other tables David G. Johnston <[email protected]>
@ 2023-02-09 16:59 ` Andreas Joseph Krogh <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2023-02-09 16:59 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected]
På torsdag 09. februar 2023 kl. 17:44:25, skrev David G. Johnston <
[email protected] <mailto:[email protected]>>:
On Thu, Feb 9, 2023 at 12:24 AM Andreas Joseph Krogh <[email protected]
<mailto:[email protected]>> wrote:
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.
--
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to ensure a log-entry is created based on state of data in other tables
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
@ 2023-02-09 16:44 ` Rob Sargent <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2023-02-09 16:44 UTC (permalink / raw)
To: [email protected]
On 2/9/23 00:23, Andreas Joseph Krogh wrote:
>
> 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?
>
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-02-09 16:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-09 07:23 How to ensure a log-entry is created based on state of data in other tables Andreas Joseph Krogh <[email protected]>
2023-02-09 15:08 ` Steve Midgley <[email protected]>
2023-02-09 16:33 ` Andreas Joseph Krogh <[email protected]>
2023-02-09 16:37 ` Steve Midgley <[email protected]>
2023-02-09 16:44 ` David G. Johnston <[email protected]>
2023-02-09 16:59 ` Andreas Joseph Krogh <[email protected]>
2023-02-09 16:44 ` Rob Sargent <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox