RE: Normalised Stock Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Fred, thnaks for the response - replies inline.

"Fred" wrote:

Hello TonyT,

First I think that you're going to have to noodle on a couple of foundation
items, with respect to the issue at hand, what is your mission for the
database?

The current version is an Invoicing & stock control system set up to handle
retailing both spare parts and tools & equipment (currently handled
separately), with reporting and historical lookup information for stock and
customer purchases etc.. More of my customers are diversifying into other
fields such as general hardware sales & hire equipment to name but two.

Rather than having to add additional functionality to meet the needs of each
*new* area of retailing I'm trying to build a database that can handle
different different products within the same framework.

With respect to "bought and sold in quantities" are you out to track
inventory and transactions based on the little items, or are you treating
"packages" as entities? If it's the former, then a 6 pack of spark plugs is
really just 6 individual spark plugs. If it's the latter, then the "6 pack"
is the inventoried entity, which will have a different part number than the
individual spark plugs, and any conversion between the two (packs vs.
individuals) will be a transaction ("creating" one, "destroying" the other)
which needs to be tracked.

Both scenarios are regular occurences & are handled by cross-reference and
stock adjustment tables - a record is kept for each different item/part by
its own item/part number (some items have a separate number for a box of ten,
others will be ordered as 10 of single item) The cross referencing is a
junction table with 2 instances of the stock table and another child table
with the *reason* for the cross reference. (thanks to you for the help on
sorting that out a few months ago!)

Next you have to define your mission in detail with respect to transaction
tracking and inventory tracking of individualized/serialized items and items
which are not so. I know that you know your general wish in this area, but
you'll need to get specific. Here's my guess at an answer: For part number
which has been identified as one which requires individualized/serialized
tracking, cause all transactions and tracking to occur at the individualized
level.

Correct

Assuming that my guessed answer is right, your dilemma isn't one of not
knowing structure, it's more fundamental, that a table (be it inventory or
transactions) really should have only one "what is a record" definition,
whereas you have two fundamentally different ones (using inventory table[s]
as an example):

1. Record is a description of the part and a numerical inventory summary
2. Record is an instance of stocking of on (individualized) item.

One way to reconcile the two is to use a standard inventory transaction
application (like Allen's) except to consider the "identity" of an item to be
a concentation of it's part number and serial number. When there is no
serial number, then the part number alone will identify the item. Move
your inventory count data into what you now consider to be your "serial
number" table, but this is now your inventory table; the serial number field
will be blank on those items that do not get individualized tracking. Your
old main table now becomes just a static table with data on basic part
numbers. Add a "RequiresIndividualTracking" field to tag a part number as
such. Such would trigger a few things, including the serial number field
becoming a "must enter", and transactions could only be quantity 1 etc.
Looking up the (composite) part number would be required for a transaction.
So, an addition of a serialized item would always be a "no record found" (=
new record required) or you could have it automatically jump to that
whenever adding a individualized item.

Pretty much my first approach to resolving the issue. But I'm unclear on the
contents of the inventory table - you suggest a blank serial number entry for
Spare Parts (I'll use Spare Parts from here on in for items that are
un-serialised & bought & sold in multiples), and all items are added
singularly to that inventory table. Does that not create unnecessary
duplication of Part Number information, especially when one customer i saw
revcently has circa 80k part numbers with multiple quantities of most of
those?

Hence my possible aproach of adding multiple lines to the existing product
table only for the serialised items with a 1 to 1 join to the serial number
table, less normalised, but less duplication also?

Just an idea plus a few thoughts.

many thanks again


Fred


.

Relevant Pages

  • Re: Rqst for Inventory Database Best Practices
    ... > I am developing an inventory database. ... What products are in stock. ... Track only transactions and calculate the stock ...
    (comp.databases.theory)
  • RE: Inventory
    ... You can do an update query to adjust the quantity value in your inventory ... bill should probably include the ability to have a qty for each bill item. ... link to the Bill of Materials table to release those items from inventory. ... > I need it to subtract from my Inventory Transactions table per the example ...
    (microsoft.public.access.tablesdbdesign)
  • Rqst for Inventory Database Best Practices
    ... I am developing an inventory database in SQL Server. ... What products are in stock. ... Warehouse operations (e.g., receipt of delivery, inspection, add to ... calculate the stock based on the sum of all transactions. ...
    (comp.databases.ms-sqlserver)
  • Rqst for Inventory Database Best Practices
    ... I am developing an inventory database. ... What products are in stock. ... Warehouse operations (e.g., receipt of delivery, inspection, add to ... calculate the stock based on the sum of all transactions. ...
    (comp.databases.theory)
  • RE: Normalised Stock Table
    ... Next you have to define your mission in detail with respect to transaction ... tracking and inventory tracking of individualized/serialized items and items ... cause all transactions and tracking to occur at the individualized ...
    (microsoft.public.access.tablesdbdesign)