Normalised Stock Table

[TonyT]

Hi,

I'm trying to figure a way of having a single stock table that can handle
items that are bought and sold in multiples (eg. Spark Plugs bought by the
hundred) for which I use a modified version of Allen Browne's OnHand function
to calculate current stock quantity from Invoice & BookIn tables. So each
product line only has one record in the table. However I want to include
Wholegoods in the same table (eg a Powertool with a distinct serial number),
these items are also often bought in quantity, but usually in 2's or 3's.

They currently reside in separate tables, but this makes for more work in
ordering, invoicing, cross referencing and historical purchase history.

If I keep the serial number info in a child table of the stock table, I run
into the same issues as I have now with separate tables, whereas if I have a
record in the stock table for each wholegood item it makes for a *lot* of
duplicted data & I have to work around counting stock differently for
different items in the same table, which feels like bad practice!

I've tried so many different table designs/layouts, but none allow me to
relate them all together with ease, currently I have about 60 tables all
nicely normalised, but this one is letting me down!

 

[Fred]

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?

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.

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.

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

Just an idea plus a few thoughts.


Fred

 

[TonyT]

Hi Fred, thnaks for the response - replies inline.

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

Hello TonyT,

You have a lot going on there , probably too much to describe or absorb in
posts, and such description is probably unnecessary. as it sounds like it's
mostly working nicely.

I'm afraid that I failed to notice an apparent conflict on an important /
fundamental item in what you said and failed to ask for a clarification.

The question is, in general, which of the following two are you doing:

1. Have a "current inventory" in (a) table(s) and then making modifications
to it by transactions. This is what you mentioning an "inventory" table
implies.

2. Just storing transactions, and then deriving current inventory by
summing them in a report etc. Having looked at it only briefy, I think that
this is what Allen's DB concept (that you mentioned that you are doing) uses.


I'd have to know which of the above you're doing in order to make a specific
reply to your question, but here's a vague reply.

Your "Parts" table would have all of the info for each part #, and would
have only one record per part, plus the one field that tags it as a
serialized type part. And so all of this info would never be duplicated.

Your "inventory or transactions" table (there's the vagueness) could have as
few as these three fields:

PartNumber (FK)
Quantity
SerialNumber

Plus maybe an autonumber PK.

I think that this would be normalized / avoid duplicaton of data, and avoid
there being any more records than necessary.

Sincerely,

Fred

 

[TonyT]

answers inline again,

Hello TonyT,

You have a lot going on there , probably too much to describe or absorb in
posts, and such description is probably unnecessary. as it sounds like it's
mostly working nicely.

I'm afraid that I failed to notice an apparent conflict on an important /
fundamental item in what you said and failed to ask for a clarification.

The question is, in general, which of the following two are you doing:

1. Have a "current inventory" in (a) table(s) and then making modifications
to it by transactions. This is what you mentioning an "inventory" table
implies.

not this way, sorry I picked 'Inventory' from your response incorrectly

2. Just storing transactions, and then deriving current inventory by
summing them in a report etc. Having looked at it only briefy, I think that
this is what Allen's DB concept (that you mentioned that you are doing) uses.

Correct, calcualtions are made as QtyBookedIn - QtySold +/- stock
alterations (after last stock take)

I'd have to know which of the above you're doing in order to make a specific
reply to your question, but here's a vague reply.

Your "Parts" table would have all of the info for each part #, and would
have only one record per part, plus the one field that tags it as a
serialized type part. And so all of this info would never be duplicated.

Your "inventory or transactions" table (there's the vagueness) could have as
few as these three fields:

PartNumber (FK)
Quantity
SerialNumber

Plus maybe an autonumber PK.

I think that this would be normalized / avoid duplicaton of data, and avoid
there being any more records than necessary.

I had similar to above as Items table to store (multiple) individual
serilised items of a Product in the product table, then linked that table to
a Serial Number table & others. But, and this is my sticking point, I need to
factor in an either/or situation for BookIn, Invoicing, Cross Referencing,
Stock Quantity and various other relationships to determine whether the
'thing' being sold is an 'Item' or a quantity of a 'Product', otherwise I
can't determine which child of the Products table I'm dealing with.
It seems like my choices are;

either, I either create a separate table for Products and Items entirely
(and link those to each of the BookIn, Invoicing etc tables), having each
record in the Items table representing an indivual 'piece of equipment' with
serial numbers. (ie. Stock Quantity would be Count of * in Items....)

or, I keep them together in the same (Product) table with the child
SerialNumber(instead of Items) table, this still requires the separate links
to BookIn, Invoicing tables etc, and gives more work in determining quantity
as there are 2 different *sets* of data in 1 table.

(sorry, not helped myself or descriptions much by calling one table Items)

I can't see any way of having everything in just one products table and
still being able to have a single link to the other (BookIn, Invoice etc)
tables.

thanks again,

TonyT..

 

[Fred]

Hello Tony T,

I think that the concept that I suggested will successfully core issue that
you are wrestling with. (not sure on how it realtes to the rest of your
application) But, from your post, I don't think that I have communicated
it. With the new info that you provided, I can recap one item more
specifically. You would have a part number table, plus that "three field"
transactions table. The "Part number" for the the purposes of recording
the transaction a combination (concantation) of the part number and the
serial number.

Not sure what else to say other than to answer any specific quesitons.

Sincerley,

Fred

 

[TonyT]

Hi Fred,

I agree that we maybe are not understanding each other fully, and I
appreciate your continued effort.

Perhaps one question will make it clearer for me, what link (child field) do
I have in my ItemInvoice table to be able to record the sale of either
10xproducts or 1 of the indivdual serialised numbers? By my understanding of
your responses I thought I'd need a link to both the serial number table PK
ID and another to the PK of the Products table, otherwise how do I determine
the individual (many side) Item (with the individual serial number) from the
Product table (the table that I can sell multiples from)?

ok so thats 2 questions & I cheated :/

TonyT..

 

[Fred]

Hello TonyT

For simplification, let's say that income and outgo are in 1 unified
"TransactionItems" table. This shows the general idea, I'm sure that I got
some n

You'll link your PK Partnumber field in the "products" table to the FK
"artnumber filed in your TransactionItems table.

Then make a query from them which includes an expression field which is
something like
"[Products].[PartNumber]&nz([TransactionItems[.[SerialNumber]) and display
that in your form.

Now you have part # ABC which is not serialized, and part # XYZ which IS
serialized.

And let's say you have an instance of a transaction of Part#XYZ, serial #
1234



For the purposes of entering or looking up a transaction, the "identifier"
of part number ABC is ABC. The identifier of that instance of a XYZ part
is "XYZ1234. "ZYZ" alone is not an identifier for this purpose, and is not
allowed to be entered as a transaction, I.E. user is forced to enter a serial
number for serialized parts.

Sincerely,

Fred

 

[TonyT]

Thanks again Fred,

That clarification explained your approach perfectly, I'll ponder on the
ramifications and see if it can fit the rest of the design.

TonyT..