Inventory System

[Rawb]

Hello all, it's been a while!



Anyway, I've decided to begin reworking my company's database (which is an ENORMOUS task) and have run into a few hangups. The first of these hangups is the inventory system.

I'm trying to figure out the best way to set up the table(s) for our inventory. Currently, there are two "main" types of inventory item: Glass and Non-glass.

The Glass inventory items are stored based on the following characteristics (which are ONLY used for glass inventory items):

• Height (number, ##0.000)
• Width (number, ##0.000)
• Color (number, integer ID field connected to a "colors" table)
• Light Transmission/Transparency (number, #0%)
• Thickness (number, 0.000)

My question is this: Do I use separate tables for the Glass inventory and the Non-glass inventory? Or should I combine the two and just leave a bunch of null fields for the non-glass inventory items? There are about 100 different glass items in our current inventory and about 400 non-glass inventory items.

[ajetrumpet]

My question is this: Do I use separate tables for the Glass inventory and the Non-glass inventory?

Absolutely not. I would say there is no point. Personally, I would see an input form, which a checkbox indicating glass or no glass, and maybe a subform with additional inputs that change via the change in the checkbox. And of course, all the details should be stored in a products table, and then a product details table, if detail information is abundant (linked by an id or something).

In my inventory example on the website, I have two tables only, in and out. and everything else is calculated dynamically, like valuation and stock levels.

That method to me, makes perfect sense. And the MS template in NW sort of does it this way, but it's a little more complex and cumbersome, some of which I don't understand.

[SteveF]

Instead of two tables ("in" and "out"), you can do it with only one -- just add an additional column for "direction". Make it an integer column, and populate it with one of two values: +1 for incoming, and -1 for outgoing.

Current inventory is then a simple formula in a query: Sum(Quantity * Direction)

Steve

[ajetrumpet]

Instead of two tables ("in" and "out"), you can do it with only one -- just add an additional column for "direction". Make it an integer column, and populate it with one of two values: +1 for incoming, and -1 for outgoing.

Current inventory is then a simple formula in a query: Sum(Quantity * Direction)

Steve

Nice addition Steve. Certainly a good consolidation method. I don't think I would use though, because I wouldn't be prepared to write the code that would be involved to calculate any of the numbers needed outside of a consolidated stock count.

Regardless though, nice! It's not far from the idea that started it all.

[Rawb]

First, some clarification: When I said "inventory" I was talking about a "list of parts that we have in the plant" not necessarily a "list of all the counts of parts."

That said, it sounds like you think I should make one main table for the "Part Master" list of items like the following:

PartID (text, 11 chars, primary key) - unique part no
PartDesc (text, 128 chars) - description of part
PartStockroomID (integer, index) - ID of the stockroom where this part is stored, linked to Stockrooms table
PartSupplierPartID (text, 32 chars) - manufacturer's part no
PartSupplierID (integer) - ID of the manufacturer that we purchase the part from, linked to a Suppliers table.
PartIsInventoried (boolean, index) - True if the part is counted at month-end inventory
PartIsActive (boolean, index) - True if the part is used in production, false if old product that we have but no longer use/sell
PartHeight (number, ##0.000) - Height (in inches). Used for glass ONLY
PartWidth (number, ##0.000) - Width (in inches). Used for glass ONLY
PartColorID (integer) - ID of the color, linked to Colors table. Used for glass ONLY
PartLT (number, #0%) - percentage of transparency. Used for glass ONLY
PartThick (number, #0.000) - Thickness (in inches). used for glass ONLY
PartIsFalloff (boolean, index) - Is the glass a "remaining portion" of a purchased sheet (that can be reused). Used for glass ONLY
PartIsTempered (boolean, index) - Is the part tempered. Used for glass ONLY *Used for compatibility with previous system, technically not needed*
PartIsSilkscreened - (boolean, index) - Is the part silkscreened before tempering. Used for glass ONLY
PartSilkscreenID - (text, 11 chars) - If PartIsSilkscreened is true, the part no of the silkscreen ink used
PartUMPurchID (integer) - Unit of measurement used when purchasing more of the part, linked to a UMConversion table
PartUMUseID (integer) - Unit of measurement used when producing items from the part, linked to a UMConversion table
PartLeadTimeM (integer) - Minimum lead time (in days) when producing from the part
PartLeadTimeP (integer) - Minimum lead time (in days) when purchasing the part

And then just leave the following fields empty on non-glass records:
PartHeight
PartWidth
PartColorID
PartLT
PartThick
PartIsFalloff
PartIsTempered
PartIsSilkscreened
PartSilkscreenID

Is that correct?

EDIT: Re-reading your replies. . . I'm actually looking for how I should set up what ajetrumpet called a "products table". Again sorry for the confusion!

[SteveF]

Nice addition Steve. Certainly a good consolidation method. I don't think I would use though, because I wouldn't be prepared to write the code that would be involved to calculate any of the numbers needed outside of a consolidated stock count.

Regardless though, nice! It's not far from the idea that started it all.

Even beyond a consolidated stock count, on-hand quantity for each item can use that same formula in a Totals Query, grouping by itemID. A quick check for "do we have enough on-hand to fill the order that just came in" is similarly easy.

Shipped and received quantities can be isolated by a WHERE clause filtering on the Direction column (Shipped: "WHERE Direction = -1"; Received: "WHERE Direction = 1").

What else did you have in mind?

Steve

[SteveF]

First, some clarification: When I said "inventory" I was talking about a "list of parts that we have in the plant" not necessarily a "list of all the counts of parts."

That said, it sounds like you think I should make one main table for the "Part Master" list of items like the following:

PartID (text, 11 chars, primary key) - unique part no
PartDesc (text, 128 chars) - description of part
PartStockroomID (integer, index) - ID of the stockroom where this part is stored, linked to Stockrooms table
PartSupplierPartID (text, 32 chars) - manufacturer's part no
PartSupplierID (integer) - ID of the manufacturer that we purchase the part from, linked to a Suppliers table.
PartIsInventoried (boolean, index) - True if the part is counted at month-end inventory
PartIsActive (boolean, index) - True if the part is used in production, false if old product that we have but no longer use/sell
PartHeight (number, ##0.000) - Height (in inches). Used for glass ONLY
PartWidth (number, ##0.000) - Width (in inches). Used for glass ONLY
PartColorID (integer) - ID of the color, linked to Colors table. Used for glass ONLY
PartLT (number, #0%) - percentage of transparency. Used for glass ONLY
PartThick (number, #0.000) - Thickness (in inches). used for glass ONLY
PartIsFalloff (boolean, index) - Is the glass a "remaining portion" of a purchased sheet (that can be reused). Used for glass ONLY
PartIsTempered (boolean, index) - Is the part tempered. Used for glass ONLY *Used for compatibility with previous system, technically not needed*
PartIsSilkscreened - (boolean, index) - Is the part silkscreened before tempering. Used for glass ONLY
PartSilkscreenID - (text, 11 chars) - If PartIsSilkscreened is true, the part no of the silkscreen ink used
PartUMPurchID (integer) - Unit of measurement used when purchasing more of the part, linked to a UMConversion table
PartUMUseID (integer) - Unit of measurement used when producing items from the part, linked to a UMConversion table
PartLeadTimeM (integer) - Minimum lead time (in days) when producing from the part
PartLeadTimeP (integer) - Minimum lead time (in days) when purchasing the part

And then just leave the following fields empty on non-glass records:
PartHeight
PartWidth
PartColorID
PartLT
PartThick
PartIsFalloff
PartIsTempered
PartIsSilkscreened
PartSilkscreenID

Is that correct?

EDIT: Re-reading your replies. . . I'm actually looking for how I should set up what ajetrumpet called a "products table". Again sorry for the confusion!

Yes, you can do it this way, or have two tables: one with all of the "common" attributes, plus a boolean "IsGlass". Those items where IsGlass = True would then have the glass-specific attributes stored in another table.

Either way seems fine to me...

Steve

[ajetrumpet]

Yes, you can do it this way, or have two tables: one with all of the "common" attributes, plus a boolean "IsGlass".

even with the setup that was already displayed, there should be an "isglass" field to indicate the part type. If there isn't, how else will you be able to distinguish records? Check for NULLS in the last few fields? That's not really practical, because of issues like user input error or missed inputs.

[Rawb]

Thanks for your replies.

I think I'm going to try the two-table solution. I have a question though about product costs.

In the old database, there was a separate table for product costs. Should I keep that split out into it's own table or would it be a good idea to include that data in these tables as well?

Currently, we have the ability to purchase several different sizes of glass, each one with a slightly different "cost per UM." If I include the costs in these product tables, then I'll need to use multiple records for some of our glass types (glass type "A" comes in two sizes so it needs two product records even though both sizes use the same ID).

The more I look at this, the more I think it may need two separate "inventory/product systems" - one for glass and a completely different one for non-glass. . .