[AccessD] DB design Brainteaser

Jim Dettman jimdettman at earthlink.net
Thu Sep 25 08:26:29 CDT 2003


Gary,

  I'm going to add comments to your last post.  After you review these and
respond, let's layout one more design in full before going further.  After
that we need to stand back and look at it and make sure that we can answer
all the possible questions that might be asked (output required).

  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
and Lots.  But here, Lots means something different, so I can't use the same
terms I normally do.  I've already caught myself a few times typing
"LotTrackID" rather then "LocTrackID".   If you can think of different terms
we might use for these tables, it may be helpful.




<<
tblGrowers – Ties an Owner to a field as Primary Grower.
Needed for communications - DualPK

FieldID – PK1A

OwnerID – PK1B
>>

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

<<
**** New Table ****

tblMasterLocations – General Storage Location area used to
limit user choices during input and for reporting purposes.
There may be bins with the same numbers at multiple master
locations.

MasterLocationID – Autonumber - PK

MasterLocation – Text(15)



*** Added to tblLocations ***

MasterLocationID – Long - FK
>>

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



<<
*** New Table ***

tblInvTranTypes – Lookup Table for InvTranTypes

InvTranID – Autonumber – PK

InvTran – Text(15)
>>

  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.


<<
***

In the tblInvTransactions there were actually 2 TranType
fields. I renamed the first on to InvTranID to store the
value from the new Lookup table and renamed the second
In/Out one to just Tran so not to confuse with the above.
>>

  OK.



<<
***

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.

<<
***

There is only one product, grass seed, so I have made some
modifications to what you had for Product.



tblSpecies - One record per product
SpeciesID - Autonumber - PK
Species - Text
****SpeciesTypeID - Long - FK**Not Needed. Variety will be a
new child of Species

****StockUOMID - Long – FK ** Not needed

tblVarieties - One record per product group (don't know if
you need this
or not) ** Yes
VarietyID - Autonumber – PK

SpeciesID – Long - FK
VarietyName - Text


ProductID changed to SpeciesID in tblLots

***
>>

  OK.


<<
Ok, time for a few questions.

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)

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

   Shelf life.

<<
3) As we have no tie in or relation to tblLocTrack may I
assume that this table is updated programatically on the
Update of the tblInvTransactions?
>>

  Yes.  tblLocTrack is a result of a user indicating where something was
put/pulled from.  A record in tblInvTransaction would be the record of that.

<<
4) Assuming yes to above, we would then create a new record
if we find no existing LotID/LocationID in the table and
then delete an existing record when we take the LocTrackQty
down to zero?
>>

  Yes, typically that's the way it would work.

<<
5) Same table, could we not just use LocationID/LotID as a
dual PK or do you find it useful to have the autonumber PK
for other purposes? tblOwners seems another candidate for
this treatment.
>>

  This is a six one way half a dozen another type of thing.  If your a big R
purist, then the autnumber does not belong there in the first place. Coming
in from the surrogate approach, you stick a meaningless key on everything.
In the case of a linking table however, this is just a waste of a field and
creates an additional index that needs to be maintained.  The only time that
would be of benefit is if you need to refer to that relationship from
another table (you needed a FK).

  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.

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


Jim ( and interested others ),

<<snip>>





More information about the AccessD mailing list