Gary Miller
gmiller at sistersnet.com
Tue Sep 23 10:56:38 CDT 2003
Jim, I'll try and clarify a couple of things and expound on questions inline below... ----- Original Message ----- From: "Jim Dettman" <jimdettman at earthlink.net> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Tuesday, September 23, 2003 5:38 AM Subject: RE: [AccessD] Fw: DB design Brainteaser > 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? *************** Section was probably a bad choice of words. I did mean it to be 'part of a lot'. If a lot is 20,000 lbs. with multiple owners, one owner who has a 10% stake may elect to sell their 2,000 lbs or even just 1,000 lbs of it. The lot will now be reduced in total size, inventory will need to be reduced at the location it is taken from and that owners share will have to be flagged as all or partially allocated/sold. The Company will now have ownership of the 2,000 lbs although the Lot number will not change with the ownership change as it stays with the product for the rest of it's life. The location would probably change at this point though. If the company sells it's 2000 to an outside party, only then will it actually leave inventory. As each owner of the lot will technically have their own 'Contract' on their portion of the Lot, possibly this could be handled at that level ********** > << 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. ********** And this is where I really need a bit of conceptual help. How do I approach this without having to create a record for every pound. Two things that will be critical to any given pound in a lot at any given time are the ownership and the location as well as possibly the processing state (raw or cleaned), although processing state probably belongs as a property of the parent Lot. In some ways it seems to beg some type of Parent/Child relationship, but I have not quite visualized it. In my understanding many inventory systems would have a record for every Part and would update the OnHand status transactionally. Here I do have one Part/Lot number, but it almost seems that I need one or more sub-heirarchys to track the ownership and location shifts. It also seems to want a transactional history of some type. ********** > > << 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? ********** This would be a big bonus, however the Company has stated that it would be sufficient to have the inventory status only on a 'Now' basis. They seem to be fine with periodically recording on hard copy for their records. I agree with you that a system that allowed historical pictures would be highly preferrable. ********** Gary Miller