[AccessD] DB design Brainteaser

Jim Dettman jimdettman at earthlink.net
Thu Sep 25 14:27:57 CDT 2003


Gary,

<<
>   One other thing.  I'm sure I'm going to mess up a few
times in referring
> to Locations and Location Tracking as normally I refer to
this as Locations

> "LotTrackID" rather then "LocTrackID".   If you can think
of different terms
> we might use for these tables, it may be helpful.

Location (Loc) works for me. StorLoc or Storeage could work
also. I guess I don't have the confusion that you do there.
Certainly can't change the 'Lot' word usage on my end.
>>

  I guess we'll stick with what we have for now.

<<
>   So there are field owners and then growers that actually
do the work? or
> is the "primary grower" always one of the owners?

Yes there are. The grower may or may not be an actual
part/whole owner of the field, but the grower will always
own a share of the lot so we are not dealing so much with
field ownership as field harvest ownership.

On many of the documents that will be needed the grower is
specified in the header and then the ownership breakdown is
listed below so that grower will be listed again. As a
grower will always be an owner, my approach to this I think
will be to do a 'qryGrower' query based on the tblOwners and
the tblGrower(FieldID&OwnerID) to create contrived fields to
convert OwnerFirstName, etc... to GrowerFirstName so that I
avoid having more than one address, name, etc... fields when
I get to the reporting end.
>>

  I would suggest then:

tblIndividuals - One record per person
IndividualID - Autonumber PK
FirstName
LastName
Address
City
State
Zip
Phone
Fax
taxID
etc..

tblFieldOwners - One record per individual per field
FieldID - PK1A
IndividualID - PK1B
SharePercentage

and add to tblFields:
GrowerID - FK (link to tblIndividuals)

 If you need multiple growers for a field, then we'll need a
tblFieldGrowers, with one additional field to indicate the primary grower
for a field.


<<
 >tblLocations was the "master location list" - no need for
another table.

Actually, I think that I do need to specify a larger area to
then to be able just to select the specific areas contained
within that site. Maybe the word 'Master' was confusing. The
clients would like to be able to pick a facility or facility
section and then have only the storage choices for that
facility be available. I have always used a two table
approach to doing this although I know that it can probably
be approached through one. Perhaps I should refer to it as
'tblFacilities' and then have a location store a FacilityID.
>>

  No, that's perfect.  Facilities or Warehouses is what is normally used.  I
myself like facilities.  So yes, we'll have a tblFacilities, and
tblLocations will include a FacilityID as part of it's PK.

<<
>   OK.  Generally the transaction types are limited in the
sense that they
> are tied to the programming, so it's a fixed list, but if
you want to have a
> lookup table for a fuller description that's fine.

Think that I am with you there. The transaction can be
generated from one of a variety of forms and coded to do
what is necessary in each case. I may not need that table
then.
>>

  Yes, that's it.

<<
> After discussions with the company I will probably drop
the
> UnitOfMeasure as they always refer to and price the
product
> by the pound no matter if it is loose or in whatever type
> container.
> >>
>
>   I would recommend that they not do this. One of the
reasons is that it
> allows for controlling of the number of decimal places.
If I have a UOM in
> terms of stocking, Purchase, and Selling, I can easily
convert back and
> forth between UOMs.  This might make the system simpler to
use as well.  For
> example, we might stock in lb, but the salesmen like to
deal in hundred
> weight because it would be easier to give a selling price
of a .56 rather
> then .0056.
>
>   It's also entirely possible that they may wish to
buy/sell/trade at some
> other UOM in the future

Grin... This is one of the things that I discussed with
them. They are pretty darned adamant that they don't want to
have to deal with it as they have never done anything else
in the industry in over 75 years. Prices are kind of like
stock market prices, they are what they are and they are
always in pounds even if they are in 55 lb. bags. A price is
never set until the day of the sale so that different parts
of a given Lot sold at different times by different owners
may all be different.

Maybe I should just go ahead and build in the UOM into the
infrastructure as a good programming practice and default
everything to pounds and hide the UOM from them. There
certainly would be only one choice entry.
>>

  Up to you.  including it will increase the programming a bit and of course
there is always the feeling you get when you say "I told you so"<g>.  Of
course that is said only to yourself ;)

<<
> 2) Would LocTrackExpirationDate refer to products that
have
> a shelf life or is there another purpose for this field?
> >>
>
>    Shelf life.

OK. No such thing here. Spoilage adjustments take care of
that.
>>

  Yes but how do you know that something is spoiled? Do I understand then
that there is no shelf life for a seed or in other words a "use by" date?

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: Thursday, September 25, 2003 2:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DB design Brainteaser


>   One other thing.  I'm sure I'm going to mess up a few
times in referring
> to Locations and Location Tracking as normally I refer to
this as Locations

> "LotTrackID" rather then "LocTrackID".   If you can think
of different terms
> we might use for these tables, it may be helpful.

Location (Loc) works for me. StorLoc or Storeage could work
also. I guess I don't have the confusion that you do there.
Certainly can't change the 'Lot' word usage on my end.

>   So there are field owners and then growers that actually
do the work? or
> is the "primary grower" always one of the owners?

Yes there are. The grower may or may not be an actual
part/whole owner of the field, but the grower will always
own a share of the lot so we are not dealing so much with
field ownership as field harvest ownership.

On many of the documents that will be needed the grower is
specified in the header and then the ownership breakdown is
listed below so that grower will be listed again. As a
grower will always be an owner, my approach to this I think
will be to do a 'qryGrower' query based on the tblOwners and
the tblGrower(FieldID&OwnerID) to create contrived fields to
convert OwnerFirstName, etc... to GrowerFirstName so that I
avoid having more than one address, name, etc... fields when
I get to the reporting end.


 >tblLocations was the "master location list" - no need for
another table.

Actually, I think that I do need to specify a larger area to
then to be able just to select the specific areas contained
within that site. Maybe the word 'Master' was confusing. The
clients would like to be able to pick a facility or facility
section and then have only the storage choices for that
facility be available. I have always used a two table
approach to doing this although I know that it can probably
be approached through one. Perhaps I should refer to it as
'tblFacilities' and then have a location store a FacilityID.

>   OK.  Generally the transaction types are limited in the
sense that they
> are tied to the programming, so it's a fixed list, but if
you want to have a
> lookup table for a fuller description that's fine.

Think that I am with you there. The transaction can be
generated from one of a variety of forms and coded to do
what is necessary in each case. I may not need that table
then.

> After discussions with the company I will probably drop
the
> UnitOfMeasure as they always refer to and price the
product
> by the pound no matter if it is loose or in whatever type
> container.
> >>
>
>   I would recommend that they not do this. One of the
reasons is that it
> allows for controlling of the number of decimal places.
If I have a UOM in
> terms of stocking, Purchase, and Selling, I can easily
convert back and
> forth between UOMs.  This might make the system simpler to
use as well.  For
> example, we might stock in lb, but the salesmen like to
deal in hundred
> weight because it would be easier to give a selling price
of a .56 rather
> then .0056.
>
>   It's also entirely possible that they may wish to
buy/sell/trade at some
> other UOM in the future

Grin... This is one of the things that I discussed with
them. They are pretty darned adamant that they don't want to
have to deal with it as they have never done anything else
in the industry in over 75 years. Prices are kind of like
stock market prices, they are what they are and they are
always in pounds even if they are in 55 lb. bags. A price is
never set until the day of the sale so that different parts
of a given Lot sold at different times by different owners
may all be different.

Maybe I should just go ahead and build in the UOM into the
infrastructure as a good programming practice and default
everything to pounds and hide the UOM from them. There
certainly would be only one choice entry.

> 1) Can I assume that a CycleCount refers to a physical
> Inventory Count?
> >>
>
>   No.  Cycle count is a spot check.  There are two ways to
keep an
> inventory: perpetual and physical.  With the first, you
put a plan in place
> to count x class A items, y B items, and z C items on a
daily/weekly basis.
> In other words you run through a cycle of counts.  If you
do this, then you
> can forgo a physical inventory process.
>
>   If you don't do this, then most auditing firms require a
physical
> inventory count process once or twice a year.
>
>   I put both in, but it really doesn't matter.  The
important point is that
> we have the field there to indicate why this inventory
transaction occurred.
> You may come up with different reasons (i.e. spoilage)

Gotcha

> 2) Would LocTrackExpirationDate refer to products that
have
> a shelf life or is there another purpose for this field?
> >>
>
>    Shelf life.

OK. No such thing here. Spoilage adjustments take care of
that.

> You'll notice that in tblInvTransactions, I included both
LocationID and
>LotID instead of LocTrackID.  This was done assuming that
we would delete
>LocTrack records when the qty reached 0.  If we don't do
that, then we could
>store LocTrackID instead and the autnumber would then have
a purpose.

With you there.

I will send this off now, but I am working on another post
with some ideas on the Ownership and dollar sides that I
will also post right behind.

Gary


_______________________________________________
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