Gary Miller
gmiller at sistersnet.com
Wed Sep 24 13:19:53 CDT 2003
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 >