Re: help with Object Model, relation daabase model,

From: H. S. Lahman <h.lahman_at_verizon.net>
Date: Fri, 06 Jun 2003 16:36:37 GMT
Message-ID: <3EE0C322.9080309@verizon.net>

Responding to Don...

> I'm trying to properly model a supply chain process and would like
> some help. I'll warn you that this post is fairly long and is mainly
> theoretical discussion.
>
> I need to create an object model and then realize this model in a
> relational database (Oracle). There are a large number of inventory
> records (1 million +) but I don't need to keep historical data. I
> understand that my logical model and physical model do not have to be
> the same but I'm still having trouble finding best approach.
>
> There are several types of inventory stored in several warehouses.
> Inventory types are:
> on_hand --- ready to be issued
> on_hold --- in the warehouse put on hold for some reason
> ready --- pre-packaged and will be shipped in an emergency
> owed --- stocked owed by a maintenance shop
>
> Each inventory record consists of a part_number, a location (which
> warehouse), and a quantity. I do not have serialized parts - only
> quantities for each part number.

What you are solving below is the DBA view of storing the data. The application problem solution is a quite different concern. An OO application class diagram and a DB schema are two quite different things. While they are both based on the relational data model and they may both model the same problem space, there will tend to be differences if the application has even modest business process complexity.

>
> I know of three approaches:
> -------------------------------------------------
> 1) I have one inventory record with a type attribute as shown below.
> INVENTORY
> Part
> Location
> Type
> Qty
>
> For each part/location combination I have a qty with type of on_hand,
> a Qty with type of on_hold, and so forth.

I assume you mean that the record key is {Part, Location, Type}, right?

>
> -------------------------------------------------
>
> 2) I have one inventory record with 4 Qty attributes as shown below.
> INVENTORY
> Part
> Location
> on_hand_qty
> on_hold_qty
> ready_qty
> owed_qty

This is just (1) with the {Type, Qty} dependence abstracted into unique simple domains.

Whether you use this or (1) is really a matter of DB access performance. If you are fairly likely to need two or more types at the same time, this form would probably be more efficient. The downside is schema changes if the types are volatile.

>
> -------------------------------------------------
>
> 3) I have 4 different objects, each with one quantity.
> INVENTORY_OnHand
> .....
> ....
>
> INVENTORY_OnHold
> ......

At the DB level this one violates 3NF because the same key, {Part, Location}, is used for different tables with semantically the same attribute {qty}. [One could legalize it by making them subclasses but so far in the example that would result in a superclass with no common attributes.] One test of whether multiple tables are justified is to try to break out the differences in OnHold vs. OnHand to an equivalent related table:

                1               *
INVENTORY_ITEM ----------------- INVENTORY_TYPE
Part                             Type
Location                         Qty

But this clearly does not make sense because the {Type} key is ambiguous without {Part, Location}. So this comes full circle to (1); Qty is uniquely dependent on {Part, Location, Type}.

> <snip>
>
> I would like input on how to logically model this situation and some
> input on the physical database structure. I know you will need a lot
> more information to make an effective decision on the physical model
> but we should be able to logically model the inventory with the
> information above. I also think I should be able to lay out a valid
> relational database and use materialized views to demoralize the data
> for performance.

I gather that the application is providing some sort of inventory management functionality. My advice is to solve that problem first without thinking about persistence. Then, when the data needs are defined, design the best persistence format based on DBA concerns. Finally, provide an application subsystem or layer to isolate the persistence mechanism and provide any mapping necessary to resolve differences.

For example, I would bet that at the solution level 'ready' and 'owed' inventory items have different relationships to other objects and different behaviors than inventory items that simply identify available stock (on hand, on hold). So abstract the subclasses of inventory items in the best manner for the problem solution and then worry about the mapping to a DB w/o subclassing later.

There is nothing wrong with me that could not be cured by a capful of Drano.

H. S. Lahman
hsl_at_pathfindersol.com
Pathfinder Solutions -- We Make UML Work http://www.pathfindersol.com
(888)-OOA-PATH Received on Fri Jun 06 2003 - 11:36:37 CDT

Re: help with Object Model, relation daabase model, - some theory