Re: ID field as logical address

From: Brian Selzer <brian_at_selzer-software.com>
Date: Sat, 6 Jun 2009 12:41:47 -0400
Message-ID: <f%wWl.28185$c45.3053_at_nlpi065.nbdc.sbc.com>


"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:8cfe168a-a952-4e69-a24c-09ce6035bc71_at_t10g2000vbg.googlegroups.com...
> On Jun 5, 2:10 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > "JOG" <j..._at_cs.nott.ac.uk> wrote in message
> > <snip/>
> > Question: what if the entire heading is the key. Should updates be
> > prevented altogether for such relvars?
>
> Misread - of course updates are fine.

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 inserting 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? Received on Sat Jun 06 2009 - 18:41:47 CEST

Original text of this message