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

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