help with Object Model, relation daabase model,

From: Don <dchamber_at_mindspring.com>
Date: Thu, 05 Jun 2003 21:19:41 -0400
Message-ID: <7vovdvo04b3j60hdi6kons1u56qduurnm9@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:

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. Received on Thu Jun 05 2003 - 20:19:41 CDT

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