Re: Storing state machine

>Your requirement statement is extremely simple and I suspect you have
> glossed over some of the constraints/requirements, but based on what you
> have written, your requirement seems to be trivial and easily satisfied
> with basic database facilities.

Yes. I wasn't specific. There are multiple instances of the service for
availability. The
events could reach any one of those. I think in this case isolation levels
could be key ?
An instance at any point in time can't read assuming all transactions are
committed by
all instances.

The parent order's status is used to check easily instead of checking it
children.

Thanks.

On Mon, Apr 19, 2021 at 12:21 PM Tim Cross <theophilusx(at)gmail(dot)com> wrote:

>
> Mohan Radhakrishnan <radhakrishnan(dot)mohan(at)gmail(dot)com> writes:
>
> > Hello,
> > We have a workflow when we receive events into the service.
> But we don't have a way to choreograph or orchestrate the workflow. The
> > services are all independent and receive and respond to events.
> >
> > Since there is no order imposed by the event queues I was thinking of
> storing a simple state machine in the table.
> >
> > 1. Order PENDING
> > 2. Order line 1 PENDING
> > 3. Order line 2 PENDING
> > 4. Order line 1 PROCESSED
> > 5. Order line 2 PROCESSED
> > 6. Order PROCESSED
> >
> > Order and Order lines can be PROCESSED in any order. But at the end all
> lines should be PROCESSED and then the order is also PROCESSED.
> > I won't be able to use any PostgreSql functions because we don't depend
> on those features.
> >
> > Are there any PostgreSql features that could support this pattern ? Is
> it just like any other eventually consistent pattern ?
> >
>
> What you appear to have here is two entities - orders and order items.
> An order entity has a 'state' (pending/processed) and is linked to
> one or more order items which in turn have a state.
>
> The information about order state could be derived rather than actually
> stored i.e. an order is pending if any of its order items are pending
> and is processed if all of its order items are processed. At a minimum,
> storing the order item state would be sufficient and a basic sql
> statement would be able to tell you what the state of an order is.
>
> In general, you don't want to store duplicate or redundant information
> as this can be a source of anomalies. (e.g. order state is not updated
> to 'processed' when all items are processed or is updated to processed,
> but then another item is added and for some reason, the state is not
> switched back to pending etc).
>
> in general, it is usually a mistake or poor design to use one table to
> represent different 'entities'. That is a 'bit bucket' approach which
> really degrades the ability of the database to do what it is good at -
> managing entities and their relationships.
>
> From the description you have provided, everything you need can be
> easily managed with basic SQL statements - no need for functions or
> stored procedures. All you would need is an SQL statement to insert a
> new order item, an SQL statement to update the state of an item and a
> SQL statement to report on the state of an order.
>
> Your requirement statement is extremely simple and I suspect you have
> glossed over some of the constraints/requirements, but based on what you
> have written, your requirement seems to be trivial and easily satisfied
> with basic database facilities.
>
> --
> Tim Cross
>
>
>