public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rob Sargent <[email protected]>
To: [email protected]
Subject: Re: How to ensure a log-entry is created based on state of data in other tables
Date: Thu, 9 Feb 2023 09:44:38 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>
References: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>

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?

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: How to ensure a log-entry is created based on state of data in other tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox