public inbox for [email protected]  

help / color / mirror / Atom feed

From: Steve Midgley <[email protected]>
To: Andreas Joseph Krogh <[email protected]>
Cc: [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 07:08:16 -0800
Message-ID: <CAJexoSJT_uVdMGwne8wDBEsZEMA_jfPdz41S0W9sVoj+MugOUA@mail.gmail.com> (raw)
In-Reply-To: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>
References: <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>

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

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], [email protected]
  Subject: Re: How to ensure a log-entry is created based on state of data in other tables
  In-Reply-To: <CAJexoSJT_uVdMGwne8wDBEsZEMA_jfPdz41S0W9sVoj+MugOUA@mail.gmail.com>

* 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