[AccessD] Fw: DB design Brainteaser

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




More information about the AccessD mailing list