Jim Dettman
jimdettman at earthlink.net
Thu Sep 25 14:27:57 CDT 2003
Gary, << > 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 > "LotTrackID" rather then "LocTrackID". If you can think of different terms > we might use for these tables, it may be helpful. Location (Loc) works for me. StorLoc or Storeage could work also. I guess I don't have the confusion that you do there. Certainly can't change the 'Lot' word usage on my end. >> I guess we'll stick with what we have for now. << > So there are field owners and then growers that actually do the work? or > is the "primary grower" always one of the owners? Yes there are. The grower may or may not be an actual part/whole owner of the field, but the grower will always own a share of the lot so we are not dealing so much with field ownership as field harvest ownership. On many of the documents that will be needed the grower is specified in the header and then the ownership breakdown is listed below so that grower will be listed again. As a grower will always be an owner, my approach to this I think will be to do a 'qryGrower' query based on the tblOwners and the tblGrower(FieldID&OwnerID) to create contrived fields to convert OwnerFirstName, etc... to GrowerFirstName so that I avoid having more than one address, name, etc... fields when I get to the reporting end. >> I would suggest then: tblIndividuals - One record per person IndividualID - Autonumber PK FirstName LastName Address City State Zip Phone Fax taxID etc.. tblFieldOwners - One record per individual per field FieldID - PK1A IndividualID - PK1B SharePercentage and add to tblFields: GrowerID - FK (link to tblIndividuals) If you need multiple growers for a field, then we'll need a tblFieldGrowers, with one additional field to indicate the primary grower for a field. << >tblLocations was the "master location list" - no need for another table. Actually, I think that I do need to specify a larger area to then to be able just to select the specific areas contained within that site. Maybe the word 'Master' was confusing. The clients would like to be able to pick a facility or facility section and then have only the storage choices for that facility be available. I have always used a two table approach to doing this although I know that it can probably be approached through one. Perhaps I should refer to it as 'tblFacilities' and then have a location store a FacilityID. >> No, that's perfect. Facilities or Warehouses is what is normally used. I myself like facilities. So yes, we'll have a tblFacilities, and tblLocations will include a FacilityID as part of it's PK. << > 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. Think that I am with you there. The transaction can be generated from one of a variety of forms and coded to do what is necessary in each case. I may not need that table then. >> Yes, that's it. << > 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 Grin... This is one of the things that I discussed with them. They are pretty darned adamant that they don't want to have to deal with it as they have never done anything else in the industry in over 75 years. Prices are kind of like stock market prices, they are what they are and they are always in pounds even if they are in 55 lb. bags. A price is never set until the day of the sale so that different parts of a given Lot sold at different times by different owners may all be different. Maybe I should just go ahead and build in the UOM into the infrastructure as a good programming practice and default everything to pounds and hide the UOM from them. There certainly would be only one choice entry. >> Up to you. including it will increase the programming a bit and of course there is always the feeling you get when you say "I told you so"<g>. Of course that is said only to yourself ;) << > 2) Would LocTrackExpirationDate refer to products that have > a shelf life or is there another purpose for this field? > >> > > Shelf life. OK. No such thing here. Spoilage adjustments take care of that. >> Yes but how do you know that something is spoiled? Do I understand then that there is no shelf life for a seed or in other words a "use by" date? 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: Thursday, September 25, 2003 2:02 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] DB design Brainteaser > 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 > "LotTrackID" rather then "LocTrackID". If you can think of different terms > we might use for these tables, it may be helpful. Location (Loc) works for me. StorLoc or Storeage could work also. I guess I don't have the confusion that you do there. Certainly can't change the 'Lot' word usage on my end. > So there are field owners and then growers that actually do the work? or > is the "primary grower" always one of the owners? Yes there are. The grower may or may not be an actual part/whole owner of the field, but the grower will always own a share of the lot so we are not dealing so much with field ownership as field harvest ownership. On many of the documents that will be needed the grower is specified in the header and then the ownership breakdown is listed below so that grower will be listed again. As a grower will always be an owner, my approach to this I think will be to do a 'qryGrower' query based on the tblOwners and the tblGrower(FieldID&OwnerID) to create contrived fields to convert OwnerFirstName, etc... to GrowerFirstName so that I avoid having more than one address, name, etc... fields when I get to the reporting end. >tblLocations was the "master location list" - no need for another table. Actually, I think that I do need to specify a larger area to then to be able just to select the specific areas contained within that site. Maybe the word 'Master' was confusing. The clients would like to be able to pick a facility or facility section and then have only the storage choices for that facility be available. I have always used a two table approach to doing this although I know that it can probably be approached through one. Perhaps I should refer to it as 'tblFacilities' and then have a location store a FacilityID. > 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. Think that I am with you there. The transaction can be generated from one of a variety of forms and coded to do what is necessary in each case. I may not need that table then. > 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 Grin... This is one of the things that I discussed with them. They are pretty darned adamant that they don't want to have to deal with it as they have never done anything else in the industry in over 75 years. Prices are kind of like stock market prices, they are what they are and they are always in pounds even if they are in 55 lb. bags. A price is never set until the day of the sale so that different parts of a given Lot sold at different times by different owners may all be different. Maybe I should just go ahead and build in the UOM into the infrastructure as a good programming practice and default everything to pounds and hide the UOM from them. There certainly would be only one choice entry. > 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) Gotcha > 2) Would LocTrackExpirationDate refer to products that have > a shelf life or is there another purpose for this field? > >> > > Shelf life. OK. No such thing here. Spoilage adjustments take care of that. > 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. With you there. I will send this off now, but I am working on another post with some ideas on the Ownership and dollar sides that I will also post right behind. Gary _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com