Jim Dettman
jimdettman at earthlink.net
Tue Sep 23 07:38:17 CDT 2003
Gary, << 1) As mentioned above, all parts of a given lot must retain that lot# for it's full life, including when it is sold to another company that may resell it or part of it.>> This is not all that difficult in terms of design, but it does raise the complexity of the database as "LotNo" will need to be carried internally in the inventory system. << 2) As a given Field that a Lot is generated from may be owned by one or many owners, any given Lot may have many owners. Apparently with some ownership related issues having to do with inheretinces on Indian Reservations, some Lots may have as many of 60 different owners. Actually it is the Field that the Lot is assigned to that has the owners <grin>. >> I don't see that as a problem << 4) A Lot may start at 10,000 lbs and then will end up being only 6,500 after 'Processing'.>> This is called a yield variance. It's handled by simply inventory transitioning: Raw material in - output = yield variance << 3) A given Lot may be split between many different storage bins, locations and sometimes even different states.>> Not a problem because to satisfy #1, every inventory on hand and/or transaction record will be tagged with the lot number. << 4) As I said in 2), there may be many multiple owners for any given Lots and they may share ownership of multiple Lots on a given Field. Any owner at any time can elect to cash out to the Company at any given time to cash in on Today's market price while the others do not. This reflects a need for the Company to be able to transfer/sell/earmark a section of a given Lot as the section sold without having the particular owner being assigned to a particular section of the Lot.>> That made my head spin<g>. One thing that needs to be clarified right off is the terms used. This is the first time you've used "section". It's not clear to me if a section refers to part of a lot of grain that came from a field, or if a section is part of a field that a lot came from. If that's the case, is it possible for a lot of grain to be harvested from more then one section? << 5) A Lot constantly will change size. Really don't want to go to the point of tracking every individual 'Pound' in a lot as this involves millions of them.>> But that's what you'll do in a sense. Not that you'll track every single pound, but pushing to the extreme, if the lot is process, sold, traded, etc pound by pound, then the app should handle it without modification in the design. << 5) It is critically necessary to know what product from what lots are being stored in what locations at the present time. Not so critical to pick a point in history and identify inventory quantities. >> I would think it would be. If contamination were to occur for example, one might need to know everything that was stored in location xyz from that point forward. Would something like that be a concern? Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gary Miller Sent: Tuesday, September 23, 2003 1:44 AM To: AccessD at databaseadvisors.com Subject: [AccessD] Fw: DB design Brainteaser Howdy gang. After a bit of a hiatus I am back with a major project on the chopping block and find myself in need of some learned advise from some of the valuable listers that may have had some more experience with intricate inventory systems than I have had in the past. Just came out of a 6 hour 'Overview' meeting and my head is spinning with some of the intracicies involved. Goals from the Access-Dev Group: Conceptual advice on approaches to handle the tracking, especially on the constant Inventory side and ownership tracking or online resources available for the study of this type of model. Scope: An agricultural product tracking system from pre-birth to final sale. Constant inventory tracking, periodic inventory reduction due to 'Cleaning' the product, very regular inventory location shuffling, disbursements to Owners / Growers ( not always the same ), final sales receipts and P&L. Flow: Grower contracts with company to 'process and store' the Ag product. Processing involves cleaning and purity testing of a given 'Lot' of product. Assigned Lot# needs to live with the product or fraction thereof for it's full life time. Processing will reduce the original poundage by 10-50% depending on initial purity. After this initial adjustment of quantity ( probably in pounds ) the specific lots need to be tracked throughout their lifetime in the system until sold to an outside buyer. Product put into the system may have an initial sale price through contract or the contract may specify that the buyer reserves the right not to 'sell' until a later date when market prices improve, therefore basically leaving the product in the companies storage facilities for an undetermined time. Company is always the buyer, but it is the growers discretion when to sell at prevailing Company buying prices. They can just store and gamble on the prices for an underteminate time. One grower apparently stored for seven years before deciding to sell. Product quality declined, but that can be handled easily as no price is assigned until the sale. Parts of Lots may be purchased by many different contracts. Twists <grin>: 1) As mentioned above, all parts of a given lot must retain that lot# for it's full life, including when it is sold to another company that may resell it or part of it. 2) As a given Field that a Lot is generated from may be owned by one or many owners, any given Lot may have many owners. Apparently with some ownership related issues having to do with inheretinces on Indian Reservations, some Lots may have as many of 60 different owners. Actually it is the Field that the Lot is assigned to that has the owners <grin>. 4) A Lot may start at 10,000 lbs and then will end up being only 6,500 after 'Processing'. 3) A given Lot may be split between many different storage bins, locations and sometimes even different states. 4) As I said in 2), there may be many multiple owners for any given Lots and they may share ownership of multiple Lots on a given Field. Any owner at any time can elect to cash out to the Company at any given time to cash in on Today's market price while the others do not. This reflects a need for the Company to be able to transfer/sell/earmark a section of a given Lot as the section sold without having the particular owner being assigned to a particular section of the Lot. 5) A Lot constantly will change size. Really don't want to go to the point of tracking every individual 'Pound' in a lot as this involves millions of them. 5) It is critically necessary to know what product from what lots are being stored in what locations at the present time. Not so critical to pick a point in history and identify inventory quantities. Not having a tremendous amount of experience with advanced inventory systems, I would appreciate all constructive advice on conceptual approaches to the inventory process especially, on the billing and tracking secondarily. Thanks in advance for any and all insights into what may be needed for a solid foundation, efficient approaches or any other helpful advice. P.S.: Nancy... How did I do on asking a question??? Gary Gary Miller Gary Miller Computer Services Sisters, OR gmiller at sistersnet.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com