Thread

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

   Andreas Joseph Krogh <andreas@visena.com> — 2023-02-09T07:23:59Z

   
   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
   andreas@visena.com <mailto:andreas@visena.com>
   www.visena.com <https://www.visena.com>
    <https://www.visena.com>

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

   Steve Midgley <science@misuse.org> — 2023-02-09T15:08:16Z

   On Wed, Feb 8, 2023 at 11:24 PM Andreas Joseph Krogh <andreas@visena.com>
   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
   

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

   Andreas Joseph Krogh <andreas@visena.com> — 2023-02-09T16:33:07Z

   
   På torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
   science@misuse.org <mailto:science@misuse.org>>:
   
   
   
   […]
   
   
   
   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
   andreas@visena.com <mailto:andreas@visena.com>
   www.visena.com <https://www.visena.com>
    <https://www.visena.com>
   

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

   Steve Midgley <science@misuse.org> — 2023-02-09T16:37:35Z

   On Thu, Feb 9, 2023 at 8:33 AM Andreas Joseph Krogh <andreas@visena.com>
   wrote:
   
   > På torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
   > science@misuse.org>:
   >
   > […]
   >>
   >>
   >>
   > 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?
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2023-02-09T16:44:25Z

   On Thu, Feb 9, 2023 at 12:24 AM Andreas Joseph Krogh <andreas@visena.com>
   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.
   

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

   Rob Sargent <robjsargent@gmail.com> — 2023-02-09T16:44:38Z

   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?
   

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

   Andreas Joseph Krogh <andreas@visena.com> — 2023-02-09T16:59:03Z

   
   På torsdag 09. februar 2023 kl. 17:44:25, skrev David G. Johnston <
   david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
   
   
   On Thu, Feb 9, 2023 at 12:24 AM Andreas Joseph Krogh <andreas@visena.com 
   <mailto:andreas@visena.com>> 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
   andreas@visena.com <mailto:andreas@visena.com>
   www.visena.com <https://www.visena.com>
    <https://www.visena.com>