Re: ID field as logical address

"Kevin Kirkpatrick" <kvnkrkptrck_at_gmail.com> wrote in message news:9e6fb9e1-8382-448f-b3ae-03892aecb517_at_o18g2000yqi.googlegroups.com...

<snip>

> If your data model does not allow for assertions about bin movement,
> then you cannot coherently enforce constraints on bin movement. In
> this case, the model doesn't support propositions of the form
> BIN_MOVEMENT {warehouse, row, shelf, bin, prior_warehouse, prior_row,
> prior_shelf, prior_bin [...,picker_number, movement_time,...] }
> (to be interpretted as: "The bin currently in <warehouse> in <row> on
> <shelf> labeled <bin> was moved there directly from <prior_warehouse>
> in <prior_row> on <prior_shelf> labeled <prior_bin> ...", or something
> similar).

In fact, the data model does allow for assertions about bin movement.

UPDATE Bins

    WHERE ( Warehouse = 3 AND Row = 20

        AND Shelf = 5 AND Bin = 14 )
    ( Row := 22, Shelf := 3, Bin := 18 )

represents the sentence,

"Each bin 14 on shelf 5 of row 20 of warehouse 3 is now bin 18 on shelf 3 of row 22 of warehouse 3."

And if there has up to the time of the update been a bin 14 on shelf 5 of row 20 of warehouse 3, then the sentence is the equivalent of the assertion,

"Bin 14 on shelf 5 of row 20 of warehouse 3 is now bin 18 on shelf 3 of row 22 of warehouse 3."

Also,

UPDATE Bins

    WHERE ( Warehouse = 2 AND Row = 9 AND Shelf = 5 )     ( Row := 12, Shelf := 2 )

represents the sentence,

"Each bin on shelf 5 of row 9 of warehouse 2 is now on shelf 2 of row 12 of warehouse 2."

And if, for instance, just bins 3, 7 and 10 have up to the time of the update been on shelf 5 of row 9 of warehouse 2, then the sentence is the equivalent of the set of assertions,

"Bin 3 on shelf 5 of row 9 of warehouse 2 is now bin 3 on shelf 2 of row 12 of warehouse 2."
"Bin 7 on shelf 5 of row 9 of warehouse 2 is now bin 7 on shelf 2 of row 12 of warehouse 2."
"Bin 10 on shelf 5 of row 9 of warehouse 2 is now bin 10 on shelf 2 of row 12 of warehouse 2."

So it is clear to me that since the user is able to express them, the data model /does/ allow for assertions about bin movement, even though it is not the assertions themselves but the results of evaluating those assertions in the context of the database that are actually recorded.

<plugging in your correction>

> This is only needless clutter if the client is not concerned
> about the prior location of a bin (in which case, he can't sensically
> request a constraint referring to that property).

The prior location of a bin is only relevant during a transition, once the transition has occurred, there is no more use for it, at least as far as the client is concerned.

> In short: if you want transition constraints, then model transitions,
> and constrain them.

Not all transition constraints involve updates. Some involve inserts and deletes. For instance, how would you implement a business rule that states that only empty bins can be binned? For example, the following relvar could be for the current location of inventory stored in bins,

BinInventory { Warehouse, Row, Shelf, Bin, Item, Quantity }

with key { Warehouse, Row, Shelf, Bin, Item }

and referential constraint,

CONSTRAINT BinInventory_Bins

    BinInventory { Warehouse, Row, Shelf, Bin }

            |<=| Bins { Warehouse, Row, Shelf, Bin };

Given that the key can be the target of an update and that when an update is coerced into being a relational assignment, it can no longer be determined from just the before and after values of a relvar whether an assignment is the result of an update or of a delete and an insert, how can a business rule that states, "Only empty bins can be discarded." be implemented. One must first be able to definitively determine that a bin is in fact being discarded, not moved, correct?

It is a simple matter to declare a transition constraint if delete, insert, and update are primitive operations and not shortcuts for assignment. Here's the constraint that prevents a delete that targets a bin that contains inventory:

CONSTRAINT NoDeleteNonEmptyBin

    IS_EMPTY ( BinInventory- WHERE Quantity > 0 );

> Your "transition constraint" solution is really just "take a database
> tool that isn't expected to be used for routine business procedures,
> and completely disable it for all users of all applications in all
> contexts"

In what way does the solution do that? What "tool" is disabled? The solution does not prevent the specification of any state constraints. All it does is allow transition constraints to be specified declaratively. Of course that requires that transitions be precisely characterized, which relegates relational assignment to being just a shortcut for a delete and an insert. But there is obviously no loss of expressiveness in so doing because every possible relational assignment and multiple assignment under the databases-as-collections-of-relvars paradigm can be expressed (and more precisely, I should add) as a set of primitive delete, update and/or insert operations.

> (never minding the non-routine issues that crop up from time
> to time; e.g. the sudden need to move 30 empty bins from WH1 to WH2,

I had originally sought to simplify the example as much as possible, but since the purpose of the transition constraint is to prevent /inventory/ from being moved without the necessary documentation and accounting transactions, it is a simple matter to alter the constraint so that only /empty/ bins can be moved between warehouses.

CONSTRAINT NoNonEmptyWarehouseChange

    IS_EMPTY ( BinInventory~

        WHERE NOT ( Warehouse' = Warehouse
                AND Quantity' > 0 ) );

Note that this doesn't prevent a bin from being emptied /and/ moved. If it is also a requirement that the bin be empty /before/ it can be moved, then replace

"Quantity' > 0" with "( Quantity' > 0 OR Quantity > 0 )" in the above constraint.

Note also that the NoDeleteNonEmptyBin defined above would prevent an attempt to bypass the constraint by issuing a delete and an insert.

> or the need to correct an insert of 5000 new bins into WH2 that were
> actually placed in WH3 - just assume these thing will never, ever
> happen, or clutter the database with un-requested "update explanation"
> relations and the additional complex application screens and user
> training they'll require).

What is so complicated about it? It is not essential that the explanation be supplied by the user directly: it could be supplied by the application as a response to a simple "Are you sure you want to do this?" type of message box. How is that so different from a window popping up informing them that some other user made a change while they were diddling around?

How does the addition of /one/ relation schema for explaining corrections--only one is needed to be able to differentiate between a transition and a correction, and that one can be used to account for /all/ corrections--clutter up the database? Moreover, I would argue that the explanation relation schema is not un-requested, but a consequence of the requirement of being able to correct mistakes.

> If you don't want an application to update the warehouse, then don't
> write code in that application that updates the warehouse.

Am I reading you correctly? Are you suggesting that the constraint should be enforced in the application, not the database?

<snip>

> Having said that, as this is a thread about Identities, allow me to
> make a comment about your business example, which highlights an exact
> situation where the natural world is going to dictate
> 1) a unique, scannable bin identifier permanenty adhered to each bin
> (in addition to the large numeric labels),
> 2) scannable shelf identifiers attached to each shelf, and
> 3) business practice that involves scanning both bin and shelf with
> each pick up and drop-off and
> 4) (probably) business practice that involves scanning inventory items
> taken out of and put into each bin.
> Suddenly, you've got a natural "Bin_Id" key to work with; an easy way
> to track who moved which bins, from where, to where, and when; a model
> that can be constrained so it doesn't accept a movement of bins from
> one warehouse to another, and so on, and so on...

>

The introduction of barcodes has merit, but I balk at the introduction of the Bin_Id for a number of reasons. First of all, it doesn't eliminate the need for transition constraints because just adding a supposedly "stable" key doesn't eliminate the need to prevent updates that target that key. Secondly, it doesn't eliminate the weary employee scenario because the employee could have simply failed to scan the shelf and bin, requiring the entire warehouse to be searched, though you are correct to some extent in that finding the bin in question would be at least be definitely possible. Third, I don't think it is necessary: a solution that doesn't involve christening each bin would be to simply include the warehouse, row and shelf on the label, in a smaller font, of course, and perhaps also a barcode that is a concatenation of all of the components, something like "W3R23S2B14" would be useful. Then every bin in every warehouse can be positively identified, so that when the picker is faced with two Bin 4's, he can simply read the labels to find out which bin goes where. In addition, since the system, as a proxy for management, directs the pickers not only where to pull and put away inventory, but also where to move bins, it can help ensure that no two bins have the same barcode at the same time. Lastly, it is not impossible to track bin movements over time, should that at some point in the future become a requirement, even with a volitile key. The key components at a singular event for each bin, such as when the bin was placed into service, or when the tracking requirement was instituted, combined with a reference to that event, such as a time stamp, permanently identifies the bin. That permanent identification could be used to track bin movements over time, making it possible to determine culpable parties or to track careless employees in the incidents described above. Note that a transition constraint preventing updates that target the attributes for the permanent identification would still need to be specified.

<snip> Received on Fri Jun 12 2009 - 06:33:50 CEST