[AccessD] DB design Brainteaser

Stuart,

Replies inline...
>
> You weren't planning on using the Lot Number as PK anyway
where you?
> ....*where you*?
> ....WHERE YOU?
> :-)

Actually, since I had been assured that it is like a
pedigree, I was assured that it was absolutely unique so I
was kind of thinking that I WHAS going to. Famous last
words. I know longer Whill. Were whas I? <gr>.

> >
> > 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
>
> Until someone discovers a Weigh Ticket that didn't get
entered until
> after someone else "finalised* the Lot :-(
> Believe me - it *will* happen!

Yes, I know that as well and will keep the Weigh Tickets for
a year or so and will have a Re-Do finalization routine in
place to readjust the record.



>
> >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.
> >
>
> Do they have to sell the whole LotOwnership in one go., or
can they
> sell just part of it to the Company? If so, you will need
to child
> table to record sales qtys, prices and dates.

Yes, they can sell partials. In response to this and your
comments below, you are correct. A child table will be much
more versatile and flexible than creating a second record.
This will fit into some other things that haven't been
addressed yet such as Child Support Arrears deductions
<grin> and other misc. charges that will be applied on an
Owner by Owner basis.

>
> In either case, you don't really need the flag. Default
SellDate to
> Null. Then if you have a valid date, it must be company
owned.

If I revert to the child table, I would take the date out of
that level entirely and just check that the amount owned
zeros out with the amounts sold from the child table.

> > 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.
> >
>
> A trigger would be better, on each sale, check how much is
still not
> company owned.

It would take a couple of triggers as it can all be sold by
the owners, but the Company could still have it available in
the inventory. Kind of needs to be checked from a couple of
directions. Only when the Owners have sold all shares and
the Company has shipped it to a customer can it become
inactive. Those can happen in any order.

Thanks for the comments!

Gary