[AccessD] DB design Brainteaser

Jim Dettman jimdettman at earthlink.net
Wed Sep 24 14:13:37 CDT 2003


Gary,

  It's certainly not complete; I've noticed 3 or 4 problems already in what
I posted.  Mostly because I was fleshing things out as I went along.  When
you have your questions answered by the company, bounce back and we'll get
it straightened out and run it through the wringer again.

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 2:20 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DB design Brainteaser


Jim,

What a wonderfully thought out and detailed response. I am
still studying your model and it has brought up a couple of
questions in my mind that I need to straighten out with the
Company. Back to you soon and I really appreciate the time
that you have given this.

Gary Miller

----- Original Message -----
From: "Jim Dettman" <jimdettman at earthlink.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, September 24, 2003 6:40 AM
Subject: RE: [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>>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


_______________________________________________
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