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

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?