Re: ID field as logical address

"Kevin Kirkpatrick" <kvnkrkptrck_at_gmail.com> wrote in message news:2094a406-820e-4c75-be9d-9d6d4087977e_at_y9g2000yqg.googlegroups.com... <snip>

>> Third, even if there were a reasonable way to eliminate the need
>> for the marital-status transition constraint, in no way does that prove
>> that
>> there is never any need for transition constraints. Even Date and
>> Darwen,
>> as evidenced by their "RM Very Strong Suggestion 4," acknowledge the need
>> for transition constraints.
>>
>
> Well, yes... proving negatives is impossible. On the other hand, you
> need only provide *one* example where no reasonable alternative to
> transition constraints exists (and, of course, where transition
> constraints will not require end users to lie to the database).
>

I posted this earlier in this thread, but it does seem to meet your criteria.

Suppose that you have a relvar for bins in warehouses. In this example, they are in fact plastic bins that fit on shelves in warehouses. In the interest of brevity, let's assume that the bins are all the same size and that all inventory is stored in bins. Here's some necessary additional info: there are 3 warehouses, each warehouse has between 24 and 30 rows of shelves, each row can have between 1 and 5 shelves and each shelf can house perhaps 20 plastic bins. Labels with extra large numerals are affixed to the bins so that the pickers (the employees that pull inventory to satisfy orders and put away inventory after it has been received and inspected) can rapidly locate them.

For each bin there is a tuple in the current value of the relvar,

Bins {Warehouse, Row, Shelf, Bin}

(Again for brevity, I'm leaving out the specifications of the Warehouses, Rows, and Shelves relvars, which should in fact be obvious, as well as the foreign key heirarchy, as it should also be obvious. Let's assume for now that the foreign keys defined should prevent an insert of or an update that results in a nonexistent {Warehouse, Row, Shelf} combination.)

The entire heading of Bins is its only key.

Now suppose that there a business rule that states that bins can't leave the building--that is, the Warehouse of a bin can never change. This doesn't mean that inventory can't be moved between warehouses, but for accounting purposes, such a move requires inventory transfer transactions, as well as the Bill of Lading and other documentation required by law for transporting material by truck, etc. The business rule is designed to prevent bypassing those steps.

But the fact that bins aren't allowed to be moved between warehouses doesn't mean that bins can't be moved /within/ a warehouse. In fact, the efficiency of the pickers can be increased by relocating bins to reduce the average number of rows that must be visited to satisfy an order or by reducing the number of times stairs on wheels need to be climbed to access inventory. (Moving an entire bin is best because it reduces handling of the products in the bin and is less labor intensive.)

An update can target just one bin or a set of bins. For example, in Warehouse 2 all of the bins on the top (fifth) shelf of row 9 could be moved to the empty second shelf of row 12, thus eliminating the need to climb up to access that inventory:

UPDATE Bins

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

A move of a bin might require relabeling. For example, there might already be a bin 14 on shelf 3 of row 22 in warehouse 3, so moving bin 14 from shelf 5 of row 20 to shelf 3 of row 22 would require an unused bin number to be chosen and a label to be printed. So assuming that there isn't already a bin 18 on shelf 3 of row 22:

UPDATE Bins

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

Back to the business rule. If an update is just a shortcut for a relational assignment, then how can the rule be implemented? In Oracle, I would use a row-level trigger; in Sql Server I would use an IDENTITY column and a set-based trigger, since row-level triggers are not supported. But in theory, using just the before and after values of the Bins relvar, how can one declare the transition constraint?

Perhaps you can come up with a reasonable alternative to a transition constraint, one that doesn't require extraneous information to be stored, and one that doesn't require changing the business process. Neither of the triggers mentioned above would change the business process, and the IDENTITY column would be used exclusively to preserve the stated correlation between the old and new values for each tuple targeted by an update, making it possible to join the deleted and inserted pseudotables in the set-based trigger. Changing Bin to be a candidate key would constitute a change in the business process because it would require more than two numerals to be printed on the label, requiring a smaller font and thus making it more difficult for the pickers to discern. (It's also easier to count to 20 than to 5000, and pickers are generally not rocket scientists, or they wouldn't be pickers.)

If we discard Date and Darwen's databases-as-collections-of-relvars paradigm, and treat insert, update and delete as primitive operations, then the transition constraint can be specified declaratively:

CONSTRAINT NoWarehouseChange
IS_EMPTY ( Bins~ WHERE NOT ( Warehouse' = Warehouse ) );

returns FALSE only when Bins~, which is populated only when an update is issued and only with the old and new (primed) components for each tuple actually targeted by an update, has at least one tuple in which the old and new Warehouse components differ. Received on Tue Jun 09 2009 - 07:20:41 CEST