[AccessD] DB design Brainteaser

Gary,

  Sorry I'm late getting back to you.  Let's toy around with an example and
see how well it will fit.  I've done this using a surrogate key approach.
No points off for any misses first time around<g>.

tblOwners - One record per owner
OwnerID - Autonumber - PK
OwnerFirstName - Text
OwnerLastName - Text
etc

tblFields - One record per field
FieldID - Autonumber - PK
FieldName - Text
FieldLocation - Text (not sure how you'd specify this)
etc.

tblFieldOwners - One record per field/owner pair - This is a many to many
linking table.
FieldOwnerID - Autonumber - PK
OwnerID - CK1A
FieldID - CK1B

  NOTE: As I mentioned at the top, the approach is using surrogate keys.
Here, it's really worthless.  The approach causes additional processing in
that we need an index to validate the candidate key pair of Owner/Field (you
can only have one unique combination of each).  So you could do this:

tblFieldOwners - One record per field/owner pair - This is a many to many
linking table.
OwnerID - PK1A
FieldID - PK1B
SharePercentage - Decimal

  Which eliminates a field and an index. Depends on whether or not you need
to reference that relationship from other tables.  For consistency's sake,
let's stick with the first.


  Now let's tackle the inventory side:

tblProducts - One record per product
ProductID - Autonumber - PK
ProductName - Text
ProductTypeID - Long - FK
StockUOMID - Long - FK

tblProductTypes - One record per product group (don't know if you need this
or not)
ProductTypeID - Autonumber - PK
TypeDescription - Text

tblUOMs - One record per unit of measure
UOMID - Autonumber - PK
UOMCode - Text(6) - CK
UOMDesc - Text

  Note: this would not be the full table.  I've only specified a few fields
out of the total table that I would normally use.  The UOM table design that
I have allows for conversion from one UOM to another.  This becomes helpful
when buying/selling as the price can be based on a Purchase/Sell unit of
measure rather then the stocking one.

tblLots - One record per lot
LotID - Autonumber - PK
HarvestDate - Date/time
ExpireDate - Date/time
ProductID - FK
FieldID - FK

tblLotOwners - One record per lot per field/Owner Pair. In other words, for
each lot, who owned that field at the time the lot was harvested.
LotOwnerID - Autonumber - PK
LotID - FK
OwnerID - FK

tblLocations - One record per possible location (this defines the master
list of all valid locations to put something)
LocationID - Autonumber - PK
LocationCode - Text(6) - CK
LocationType - Integer - Value Lookup (or a lookup table) - I.e.. Rack, Bin,
Silo, Barn, etc.

tblLocTrack - One record per Lot/Loc
LocTrackID - Autonumber - PK
LocationID - Long - FK
LotID - Long - FK
LocTrackQty - Decimal - Note: in stock keeping UOM
LocTrackExpirationDate - Date/time
LastReceiptDate - Date/Time
LastIssueDate - Date/Time
LastCycleCount - Date/Time


  OK.  Up to this point, we know what we have, who owns it (not sure we have
that totally covered yet), and where it is stored.  Next is a Inventory
transaction table that tells us how the inventory gets moved about.  There
is a lot of variation here in the way things can be done and this is just
one way.  We can modify this as we go along.  I'm sure when we get into the
selling logic that we'll need to or possibly add additional tables.

tblInvTransactions - One record per inventory transaction (movement of
inventory)
InvTranID - Autonumber - PK
TranDate - Date/time
EnteredBy -
EnteredOn - Date/time
ProgramCode - Text(6) - I use this field to tag which program created a
transaction - great for trouble shooting.
TranDate - Date/time
TranType - Integer - Indicates type of document that created this trans (ie.
Balance Forward, Cust Order, PO, cycle count, physical inv adj, Keypunch
error, etc.)
DocReference - Text(12) - Reference to document # that created this
transaction (ie. Customer Order Number)
TranType - Text(1) - 'I'n or 'O'ut
TranQty - Decimal - In stock keeping UOM
LotID - Long - FK
LocationID - Long - FK

  Note that we did not use the LocTrackID, but LotID and LocationID.  That's
because tblInvTransactions is a historical table.  The records in it may far
outlive the other records in the database, so it needs to maintain a copy of
actual data.  We are breaking normalization rules a bit.  The correct way to
do this is to use LocTrackID and have a tblHistoricalInvTrans, which we move
records to when we purge from the main table.  At that time, we would copy
the actual field data from the other tables (thus creating a flat file).
It's up to you how you would want to handle it.

  I'm going to stop there and let you digest all that.  Looking back, I
think the only thing not covered is what happens when someone sells their
ownership in a lot.  I have to go back over you messages and make sure I
have this clear in my head.  Fire away with any questions you might have.

  It would also be nice if others chime in with comments.

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: Tuesday, September 23, 2003 11:57 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DB design Brainteaser


Jim,

I'll try and clarify a couple of things and expound on
questions inline below...

<<snip>>