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

How about this?

1 You have parts.
2. You have places parts can be.(locations) 3. In those places a part can be in a state. (on hold, on hand, etc.)

Table of states of a part.

    state description
    on_hold -- in the warehouse put on hold for some reason (don't use it)     on_hand -- ready to be issued
    ready --- pre-packaged and will be shipped in an emergency     owed --- stocked owed by a maintenance shop

Table of parts (really a look up table of what parts could possibly be in the system or allowable parts)
  part_number (usually have a part number or SKU -> stock keeping unit) description

Table of Locations

    Location_number description other attributes of location (eg address?)

inventory (what you have where and in what state)   location_number , part_number, quanity, state

done. New stuff comes in, you add to your inventory, Stuff goes out you subtract from your inventory, you move stuff from one state to another you update your inventory. all constrained by locations, parts, states. Send me $50.
Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Don" <dchamber_at_mindspring.com> wrote in message
news:7vovdvo04b3j60hdi6kons1u56qduurnm9_at_4ax.com...


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

>

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

>

> -------------------------------------------------

>

> 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

>

> -------------------------------------------------

>

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

> INVENTORY_OnHand

> .....

> ....

>

> INVENTORY_OnHold

> ......

>

> etc.

> -------------------------------------------------

>

> Approach 1 seems very simple and I'm leaning that way for the logical

> model.  Everything is an Inventory object and if a new type is

> introduced I just need a new record.  If I use this approach for my

> database design it will increase the rows and my queries will be

> complicated and slow.

> Showing the total on_hand and the total_on hold is difficult and very

> slow.

>

> Approach 2 seems to take 1 attribute, the quantity, and morph it into

> 4 attributes.  This does not seem to properly model the process.  If

> use this approach in the database the queries are much simpler and the

> number of rows are reduced considerably.

>

> Approach 3 logically shows 4 different objects but they seem so

> similar I'm not sure if it makes sense to treat them separate.

> Designing my database this way will result in smaller table tables but

> I will frequently have to join all the tables together and may loose

> the performance gain.

>

> I'm also not sure that the type 'owed' really belongs with any of

> this.  In the business it is different, there is no physical part and

> it may have a lot of different attributes like, due_date,

> current_location, etc.  I'm leaning to making it a different object.

>

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

>