[AccessD] DB design Brainteaser

Jim,

I know that we aren't quite polished out on the Inventory
end, but I like the concept of the dynamic records that
appear and disappear in the tblLocTrack. This was the key
component that I had not had experience with due to my lack
of inventory work.

I am starting to conceptualize the ownership shares handling
and it seems that the same type of approach may work nicely
here.

Trailers start rolling in from a given field section under a
given contract#. Weighmaster assigns the new lot number (
there goes our autonumber PK's, may still use them and force
the user assigned to be unique )and records this on all
weigh tickets from the trucks coming in from this section.
When the lot limit (55,000lbs.) is reached he then starts a
new lot and the next truckloads are allocated to the new
one, etc...

Weigh tickets are sent in to the office for processing. When
all weigh tickets are entered they will then 'Finalize' the
Lot and the total Field Run pounds will be permanently
captured as part of the Lot record as eventually the weigh
tickets will be discarded from the program. This number will
never change and is a ongoing basis as to quality of the
growers operation based on the comparison of Clean Seed
weight achieved after cleaning.

The FieldRun product is now stored somewhat indefinitely in
loose form until it is processed for cleaning. When it goes
to cleaning process it will lose 20 - 50 % of it's weight
depending on the quality of the grower's operation. It will
now come out of cleaning in a bag form and is usually stored
on pallets. All clean seed will always be in bags of varying
weights, but are always counted and referred to in pounds.

After cleaning a sample is sent for testing analysis that
will give it a purity% and quality grade. A decision is then
made whether to keep the seed purity as is or to send it in
for another cleaning to improve the purity. A third cleaning
is rare, but does happen.

After the cleaning processes are completed, we now have a
weight (Clean Seed) that will be the basis of the Owner's
shares and at that point the shares should be converted from
a percentage to an actual quantity as any subsequent loses
from spoilage or rodent damage become the liability of the
Company.

Based on what we did on the inventory side I am now thinking
that the best way to go may be take this point in the
process to create records in a LotOwnership table that would
store LotID, OwnerID, QtyOwned (Lbs.), SellDate, SellPrice,
CompanyOwned(Y/N). QtyOwned to come from the Lot
CleanSeedWeight / % of ownership. SellDate and SellPrice
would remain blank until the day that they decide to
transfer ownership to the Company. The day they sell we
would fill in the date and the price and now flag it as
Company owned.

There will be occasional additional cleanings that will
neccesitate a recalculation of these poundage numbers, but
they will always happen before any sales transactions are
allowed to occur. It should be routine as long as I always
create or recalculate existing on any cleaning entry.

Another complication that just came up and affects the above
is that sometimes an Owner will want to sell 5,000 of the
10,000 that they own of a given lot. I am thinking that if a
transaction occurs that is less than the total share value I
then prompt to check if this is an error or do they want to
close the existing share record, reduce the Qty there and
create a new one for the Qty balance. Some owners would end
up with multiple lot ownership records in this instance.

As a big reporting requirement I will need to constantly be
showing the Company's 'position' on the all of the Lots and
my thought is that this simple CompanyOwned flag will be any
easy way to pull the amount that they own and is available
for final outside sales.

As these records will never be deleted, only deactivated
with the Lot finalization, would I really need a transaction
history table such as the Inventory one?

There is another interesting twist to what happens after the
final cleaning and the percentage amounts have done a final
conversion to poundage. The Company will very routinely sell
to the outside ALL of the product and ship it away even
though the Customers have not yet actually sold it to the
Company. This is what they refer as to going into a 'Short
Position' on the product. This works because the final
ownership amounts have now been set in stone once the final
Clean Seed Weight is established and the only thing that
really remains is for the Owners to pick a day when the
market price seems to be most beneficial compared to their
financial situation. Kind of like a Futures Market.
Apparently one Owner held out for seven years after his
product was physically out the door before he did his actual
sale.

I will probably need to hold a Lot as active until the last
ownership sale is transacted in combination with it being
totally out of inventory. I need to figure a good trigger
for resetting this flag in the tblLots as I don't want to
burden all of my queries with unneccessary conditions and I
don't want to trust the users to do it. Maybe a second
maintence db linked in that I could run on a schedule at
night to do updates like this looking for nothing in
inventory and all owners settled with.

Any thoughts gratefully appreciated.

Gary Miller