Gary Miller
gmiller at sistersnet.com
Wed Sep 24 15:24:23 CDT 2003
Jim ( and interested others ), I have had a chance to go through your layout pretty well and it certainly seems like a very workable model. I will list a couple of things that I have added and then address some questions that I have. **** New Table **** tblGrowers – Ties an Owner to a field as Primary Grower. Needed for communications - DualPK FieldID – PK1A OwnerID – PK1B **** 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 *** New Table *** tblInvTranTypes – Lookup Table for InvTranTypes InvTranID – Autonumber – PK InvTran – Text(15) *** 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. *** 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. *** 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, time for a few questions. 1) Can I assume that a CycleCount refers to a physical Inventory Count? 2) Would LocTrackExpirationDate refer to products that have a shelf life or is there another purpose for this field? 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? 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? 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. I am pretty comfortable with this setup with the transaction table and the location tracking table. Enough questions for the moment. Sure I will have more as we go on. Again, thank you for your great insights and detailed work. Gary > > 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