Jim Dettman
jimdettman at earthlink.net
Thu Sep 25 08:26:29 CDT 2003
Gary, I'm going to add comments to your last post. After you review these and respond, let's layout one more design in full before going further. After that we need to stand back and look at it and make sure that we can answer all the possible questions that might be asked (output required). One other thing. I'm sure I'm going to mess up a few times in referring to Locations and Location Tracking as normally I refer to this as Locations and Lots. But here, Lots means something different, so I can't use the same terms I normally do. I've already caught myself a few times typing "LotTrackID" rather then "LocTrackID". If you can think of different terms we might use for these tables, it may be helpful. << tblGrowers – Ties an Owner to a field as Primary Grower. Needed for communications - DualPK FieldID – PK1A OwnerID – PK1B >> So there are field owners and then growers that actually do the work? or is the "primary grower" always one of the owners? << **** 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 >> tblLocations was the "master location list" - no need for another table. << *** New Table *** tblInvTranTypes – Lookup Table for InvTranTypes InvTranID – Autonumber – PK InvTran – Text(15) >> OK. Generally the transaction types are limited in the sense that they are tied to the programming, so it's a fixed list, but if you want to have a lookup table for a fuller description that's fine. << *** 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. >> OK. << *** 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. >> I would recommend that they not do this. One of the reasons is that it allows for controlling of the number of decimal places. If I have a UOM in terms of stocking, Purchase, and Selling, I can easily convert back and forth between UOMs. This might make the system simpler to use as well. For example, we might stock in lb, but the salesmen like to deal in hundred weight because it would be easier to give a selling price of a .56 rather then .0056. It's also entirely possible that they may wish to buy/sell/trade at some other UOM in the future. << *** 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. << Ok, time for a few questions. 1) Can I assume that a CycleCount refers to a physical Inventory Count? >> No. Cycle count is a spot check. There are two ways to keep an inventory: perpetual and physical. With the first, you put a plan in place to count x class A items, y B items, and z C items on a daily/weekly basis. In other words you run through a cycle of counts. If you do this, then you can forgo a physical inventory process. If you don't do this, then most auditing firms require a physical inventory count process once or twice a year. I put both in, but it really doesn't matter. The important point is that we have the field there to indicate why this inventory transaction occurred. You may come up with different reasons (i.e. spoilage) << 2) Would LocTrackExpirationDate refer to products that have a shelf life or is there another purpose for this field? >> Shelf life. << 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? >> Yes. tblLocTrack is a result of a user indicating where something was put/pulled from. A record in tblInvTransaction would be the record of that. << 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? >> Yes, typically that's the way it would work. << 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. >> This is a six one way half a dozen another type of thing. If your a big R purist, then the autnumber does not belong there in the first place. Coming in from the surrogate approach, you stick a meaningless key on everything. In the case of a linking table however, this is just a waste of a field and creates an additional index that needs to be maintained. The only time that would be of benefit is if you need to refer to that relationship from another table (you needed a FK). You'll notice that in tblInvTransactions, I included both LocationID and LotID instead of LocTrackID. This was done assuming that we would delete LocTrack records when the qty reached 0. If we don't do that, then we could store LocTrackID instead and the autnumber would then have a purpose. 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 4:24 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] DB design Brainteaser Jim ( and interested others ), <<snip>>