Re: ID field as logical address

On Jun 9, 12:20 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "Kevin Kirkpatrick" <kvnkrkpt..._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.

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). This is only needless clutter if the client is compared about the prior location of a bin (in which case, he can't sensically request a constraint referring to that property).

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

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" (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, 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).

If you don't want an application to update the warehouse, then don't write code in that application that updates the warehouse. If you have a specific application (or user) that should *never* be able to update warehouse under any conditions, then it is a security issue: REVOKE UPDATE ON BINS FROM APP_USER_NAME; GRANT UPDATE ON BINS(SHELF, ROW, BIN) TO APP_USER_NAME; REVOKE INSERT ON BINS FROM APP_USER_NAME; REVOKE DELETE ON BINS FROM APP_USER_NAME; Or, restrict your application to specific views, e.g. CREATE VIEW WAREHOUSE1 AS SELECT * FROM BINS WHERE WAREHOUSE=1 WITH CHECK OPTION
(etc.)

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...

Why would you want all of this? In your current approach, small mistakes will have catastrophic consequences. Consider what might happen if a picker, perhaps weary from a long day, accidentally moves Bin 3 to Shelf 6 instead of Shelf 7. Any subsequent activity could make this little accident difficult to detect, and difficult if not impossible to set straight (especially if there was already a bin 3 on shelf 6). Or consider the situation where a picker must move bin 4 from row 8 to row 32 and bin 4 from row 9 to row 33... he moves them in a single trip, and finds himself at between row 32 and 33 with two Bin 4's. Will he call his boss, and waste 10 minutes figuring things out with an inventory count / comparison (and risk getting docked for hurting producitvity), or will he just figures the top box was probably the one meant for row 33? As bin movements aren't tracked (remember, that information just clutters the database), there'd be no way to trace these incidents back, so you couldn't even determine the culpable parties or track careless employees in incidents like these.

I understand that, as a database designer, it isn't my place to dictate how the business should work. And I'm not saying that your approach couldn't be modeled (it could), but in the interest of keeping my source of income from risking bankrupcy, you can bet your bottom dollar I'd be asking these and a lot of other questions to the SE (with SE's boss visibly cc'd, if need be). Received on Thu Jun 11 2009 - 05:14:59 CEST