Re: help with Object Model, relation daabase model,

From: Don <dchamber_at_mindspring.com>
Date: Fri, 06 Jun 2003 00:22:10 -0400
Message-ID: <0l40ev06lt987bram0a0k0vg2171hnme9e@4ax.com>

What yu describe is approach number 1 in my original post. My post only concerned the inventory table but I do have a part and lcoation table.

My inventory table has the QTY and a state which I call inventoryType.

This seems like the way to do it on the first approach but after I loaded some data and tried a query I found it was slow and required a complicated query. I'm thinking my model was right but I need to denormalize the tables.

My inventory table has about 1.2 million rows. If I want to see the total qty on_hand from each part and location A I have this query:

SELECT part, SUM(qty)

FROM inventory
WHERE location='A'

AND state='on_hand'
GROUP BY part;

But I need to fill a table with the quantity in each state and this got more complicated. I had to have 4 SUMs in the SELECT statement and had to use a DECODE to make sure I summed the values in the correct column.

SELECT part,

         SUM(DECODE(state,'on_hand',qty,0)),
         SUM(DECODE(state,'on_hold',qty,0)),
         .... other types

   FROM inventory
   WHERE location='A'
   GROUP BY part;

This was very slow because of the Functions in select statement. This also became very hard to follow as we moved to more complicated queries that joined other tables, including a table with authorized quantities setup the same way.

When I created a table with columns for each state I had fewer rows becasue each part/location combination was only in the table once. I also had no DECODE fucntion.

SELECT part,

         SUM(qty_on_hand),
         SUM(qty_on_hold)

   FROM inventory
   WHERE location='A'
   GROUP BY part;

But I don't really like this approach. Seems like I've created attributes that are not needed.

Thanks for the input,

Don

On Fri, 06 Jun 2003 03:46:52 GMT, "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote:

>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
Received on Thu Jun 05 2003 - 23:22:10 CDT

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