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