[AccessD] Fw: DB design Brainteaser

Nancy Lytle nancy.lytle at auatac.com
Tue Sep 23 06:05:08 CDT 2003


>>>>> P.S.: Nancy... How did I do on asking a question???>>>>>>

You talkin' ta me?:)

That was one of the best laid out questions I have ever seen.  The detail
was wonderful, I only wish I had some answers for you. What a major project
to come back to, just keeping track of the lots will be a headache, not to
mention multiple owners.

Good Luck,
Nancy L.

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