If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Normalised Stock Table
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! |
#2
|
|||
|
|||
Normalised Stock Table
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[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. Just an idea plus a few thoughts. Fred |
#3
|
|||
|
|||
Normalised Stock Table
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 |
#4
|
|||
|
|||
Normalised Stock Table
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 |
#5
|
|||
|
|||
Normalised Stock Table
answers inline again,
"Fred" wrote: 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.. |
#6
|
|||
|
|||
Normalised Stock Table
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 |
#7
|
|||
|
|||
Normalised Stock Table
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" wrote: 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 |
#8
|
|||
|
|||
Normalised Stock Table
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 |
#9
|
|||
|
|||
Normalised Stock Table
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.. "Fred" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|