Working on creating a database of meat cuts for our farm

[Farmer Bill]

Believe me if I could have bought an app to do this I would have. Last time I did anything with Access was 7.0, more years ago than I care to remember and that was for a class project.

What I'm trying to do is keep track of cuts of meat stored in different freezers and then in different containers in each freezer. So I've created a table with a PK, cut_code, cut_name, pkg_weight, price_lb, pkg_price, breed_name, freezer, location, date_processed.



If I am remembering correctly, and reading Access 2007 for Dummies isn't helping as much as I'd hoped, I want to create separate tables for anything I'm going to be entering multiple times? That would be the cut_name, price_lb, breed_name, freezer, location, date_processed fields. Am I making this harder on myself than it needs to be by creating all those tables? As far as size of the database is concerned at any given time we've got anywhere from no product to as much as 3,000 pkgs of meat.

I think I can handle the forms and queries once I get the design nailed down. What would really be fun would be finding an algorithm that would allow me to query how many of a certain cut I have on hand and then how many "family packs" I could produce using the weights of the packages and optimizing for say a 10lb family pack.

Currently I'm lambing, got sows giving birth, up to my shins in mud and it's still snowing so any help would be greatly appreciated. Not having an accurate inventory is cutting down on our efficiency, costing us time and money. Like to get this project nailed down before the Farmers' Market season starts up.

[June7]

You will probably want lookup tables for Breeds, Freezers, Locations, Cuts. Then I expect one table for product records that would be various combinations of the elements you listed. Yes, I expect there would appear to be a lot of replication in some fields because of the repetitive nature of the product but at some point that can't really be avoided. For instance, a company could have several supervisors so there could certainly be several employee records with the same category value of 'supervisor'. Would it really be of benefit to create a Supervisors table with a foreign key field for the EmployeeID - maybe if there is information that applies strictly to supervisors that needs to be tracked and including those fields in Employees table would mean a lot of blank fields. In your case, I see all fields would have data. Since you have fields for price_lb and pkg_weight, don't need a field for pkg_price as this should be calculated when needed from the other two fields (unless you did intend to make this a Calculated type field in the table). Save cut_code or cut_name but not both into the Products table.

I see couple ways this can go.

1. a record for every single package produced and tracking each one individually - this means assigning a unique identifier to every package, like a VIN.

2. a record for batch quantity of same packages and then records for the distribution of quantity of packages - this means assigning a unique lot number identifier and printing on each package

Review http://allenbrowne.com/AppInventory.html

[CJ_London]

I think you'll need to expand a bit on your requirement. 'Cuts of meat' - does this mean 'all of these cuts go in this box in this freezer in this shed'. Or does in mean 'this cut from this specific animal and weighs 20 pounds is stored in this box.....'? Or something in between. Reason it matters is that a system to manage the contents of boxes/freezers/etc will be different to one managing cuts of meat. I don't know much about farming but my understanding is it is now a requirement or at least an objective to be able to track any joint of meat sitting on a shelf in a supermarket back to a specific animal.

[Farmer Bill]

Thank you both for your thoughtful responses. Not too many people developing databases in my little community and it is so helpful to have people to work with when trying to create something like this. Particularly when one hasn't even thought about relational databases since university back in the mid 90s.

June, I need to have a record of every single package and be able to track each one individually. I was hoping a PK that worked automatically would handle that for me? We may well have several packages of lamb loin chops that have the same weight. I need to have each of them referenced individually. The end goal is to make this as easy and as efficient for me to post offers on an online Farmers' Market. Say I want to offer a "family" package of those individual lamb chop packages that weighs no less than 10 lbs. I want to be able to use this database to find out how many of those family packs I can put together with the stock I have on hand and I want to be as close to 10 lbs as possible but never under.

Ajax, funny you should mention that issue. It is something we have been working to implement. Thus far we have not been able to coordinate it with the processing facility we use. It's also a difficult requirement for the USDA to enforce. For instance if someone buys a package of ground beef in the US from a grocery store, that package contains meat from a large number of animals. The origin of the package can be traced back to the processing facility but then it hits dead end. That's why the recalls you may hear about frequently involve 10s of thousands of pounds of product.

Nonetheless we would like to have the ability to track a cut of meat from a specific animal to a specific buyer in the event there ever was an issue so yes I would like to include that information for each package.

I need to manage "packages of meat" and there just doesn't seem to be any tool out there that I can adapt to the purpose. Thus the appeal for help to all of you. This project doesn't have to be pretty or have lots of bells and whistles, just needs to do the job. Thanks.

[CJ_London]

Nearest tool you might find that springs to mind is one for printed clothing, but realistically it would probably only go so far, but might give some ideas for the structure.

Probably a step too far for your current requirement but with regards items being tracked - presumably each package with have a printed label, ideally with a bar code? And are your animals already tagged with an ID? If so, would be worth considering how that would be managed now rather than later otherwise you may find you have a lot of work to undo.

With regards PK's, that would be the usual method for identifying an individual record and it's related package. Just be aware that you should not give the PK any meaning other than as a unique identifier. If you do e.g. animalID+cutID then keep these in separate fields and create a composite index.

If you are at the farmers market (a real one), have you considered how to access the data whilst away from the farm?

[June7]

Primary key can be automatically generated with Autonumber field in each table. These values could be used as the linking identifiers (primary and foreign keys) between associated tables. This value is not supposed to have any meaning other than for linking records and often users are not aware they exist. However, you do need some value that users can reference as package identifier. Whether or not you use the Autonumber or some other generated value as package identifier is a decision you must make. As an example, I work for a cruise line company and each booking is assigned a unique reference like GW7RX2 and each guest assigned unique ID like 987654321A. I have no idea if these values are also used as PK/FK. Most databases I have built did not use Autonumber as PK/FK. I usually broke the 'rule' and generated an ID with meaning and used as PK/FK. In a db tracking laboratory samples, the sample ID followed template XXXA-YYYY (ex: 0001-2016) and was also PK/FK and the sequence started over each year. That same db does have one table that generates Autonumber ID and uses it as PK/FK.

The idea of combining packages to make another product has flavor of a manufacturing/assembly type of database - not an easy one to build - and subject of numerous threads.

[Farmer Bill]

Thanks again you two.

Ajax, the packages do not have bar codes. They have the name of the cut, the weight, the processing date and a lot number. The lot number is assigned to all of the animals I deliver not to each animal as an individual so it's a start but doesn't go far enough for me. Each animal has an ID tag. Problem has been getting each tag number associated with all the packages of meat from that animal. It's not the way the facility does things with other farmers/ranchers. I ask each time and occasionally it happens, most often not. I suspect they'll end up needing to do this and it would be good to have the database ready when they do.

Can you explain, or point me towards a good explanation. of "composite index"?

Regarding the Farmers' Market, we don't have the staff to leave the animals alone for a weekend and our local options are very limited. Should the opportunity arise I will take the farm's trusty Panasonic Toughbook with me

June, I think I'm following your reasoning and will let the Autonumber function take care of keeping each cut a unique record. Thanks for your input on the package combining issue. Since I'm not looking for fancy, I think my best option might be to run this function independently. Use the information from the database to provide values to plug into the equation. Not sure where to look for that sort of an algorithm but I will poke around till I find one that works.

Do either of you go back as far as Access 7? I'm just wondering what sort of new tools, tricks, and de-buggers are available in 2007 to make my job quicker and easier. Spring is finally coming in and it looks like it will be coming in fast. Would love to wrap this up in a week, get the freezers inventoried and then get on with preparing our gardens and pastures for the season. Like a lot of things, farming consists of not much for awhile and then more than you can do in the time available. I'm rapidly running out of the "not much for awhile" time.

[Bob Fitz]

What part of the world are you working in Farmer Bill

[Farmer Bill]

We are located in the north central part of Washington state in the US. About 20 miles south of the Canadian border, 2800 ft elevation, on the western edge of an area known as the Okanogan Highlands. We've got clean air, clean water, and uncontaminated soil. We produce food people can enjoy knowing that it's about as healthy as anything available in the States.
Kind of remote, not too many people I can talk with about creating databases

[June7]

A compound index is setting two or more fields to act as a unique identifier to prevent duplicate combinations. This compound index can be used as PK/FK which means saving multiple values as foreign key in dependent table. I have only ever done that once. I do everything I can to avoid compound keys. A compound index does not have to be PK/FK. A table can have more than one compound index defined. This is why the table I mentioned earlier used an Autonumber as PK/FK - to avoid a 4 field compound key.

I started with Access 2003.

[CJ_London]

my questions were just about things to think about, I wasn't suggesting they have to be.

I go back to 97. The principles are the same because they are universal principles of good database design. With regards tools, I can't really remember how it was back then. There are new options - data macros, multivalue and calculated fields come to mind, but in my opinion are aimed at the novice who doesn't require anything too complex. They merely automate things which you had to learn about and design before, but in so doing provide more limited functionality. Similarly there are facilities for creating forms and reports quickly, but to a limited range of designs and again, potentially not that efficient in operation with large datasets.

There is a much greater range of templates available and of course there are now facilities such as this and other forums, plus sites like youtube for getting help.

It is also easier to find db designer/builders - they don't need to be local anymore - everything can be done over the internet. Yes they cost money, but not necessarily a lot and as an owner manager you do need to consider the value of your time being taken away from your core business. And any designer worth their salt should be able to come up with the goods in a fraction of the time it will take you to learn and develop a solution yourself. So if you want to do this, do it because you want to learn (or relearn) access, not to save time/money.

[orange]

Unique Composite Index

I did some google searching to see how individual cuts of meat for retail sale are identified back to original animal.
No luck --lots of info on how to slaughter, cut diagram and sanitizing etc, but not for package identification.

Good luck.


Update: I did find this from Washington State
and this from USDA but nothing very specific to the origin of the meat/product.

There was also this from the Canadian Food Inspection Agency

It seems overwhelming, too general. I id see some things related to the packaging facility, but nothing that goes back to the farm and individual animal identification (but I'm not in the business and have no experience). So treat these references as potential info.

As for your database design and scope, I'd be finding out what others do and/or what and how the state foresees farmers doing to meet the regulations.
I think you are a long way from database, tables and forms. I recommend getting the facts from the regulatory sources, building a data model and testing it (pencil and paper) and ensuring the set up does what you need; then developing a database based on that proven model.

Also, if this regulation must be met by Date X, I'm sure some group will see the automation of this for farmers/co-ops as a business opportunity.

Very interesting project.

Good luck.

[Farmer Bill]

Orange, I suspect you design databases with grace and elegance and that they are things of efficiency and beauty. Unfortunately I can't afford those luxuries right now. I need a down and dirty tool I can use right now. I certainly can't wait until the state and or feds figure out what they are doing before I come up with something that lets me know what I've got, where I've got it, and how old it is.

If I can't make this happen quickly I'll drop back to my hold position and simply create an Excel spreadsheet with a form front end and create an inventory that way. If the copy of Excel MS puts in their Office for Android supported the "form" function right now I wouldn't be here having this discussion. It would be easier for my wife and me if our Tablets would run whatever I create and right now Access isn't even included.

Ajax, you've stumbled on my humiliating secret. I'm not a spring chicken, won't tell you how old, but in a month I'll be applying for Medicare. Farming with livestock can be physically demanding. There will come a day when I won't be up to it. When that happens I want a back up position. Used to be pretty good at designing small, dynamic web sites using SQL backends. Now with the trend towards responsive design I think I could help other small farmers and artisans get a web presence. Need to get my skills current and since I need this tool I thought I could jump back into the shallow end of the pool with it. Plus we've got a Seed Bank run by volunteers that could use a database to keep track of their inventory. Yet another app that doesn't exist currently. Used to be a pretty decent one by an outfit called RainForest but it was 12 years ago with Access97 behind it. I could barely trick it to run on Win 7 so it's usefulness is pretty much at an end.

So there you have it, I'm hoping to keep my mind active, possibly make a few bucks, and help my community by bringing my old computer mind back on line. If any of you remember the VAX or terminal rooms the size of a classroom with punch card terminals and magnetic tape storage spools than you know how far back I go. I've used a Wang and built an Altair, dang I'm getting old.

As I've said before any help or points in the right direction you can provide an old reprobate would be greatly appreciated.

[Farmer Bill]

By the way Orange, I suspect you are right about someone seeing the opportunity and coming up with some tools for small farmers and co-ops. Problem is everybody is focused on the "cloud" and our area is so geographically challenged that many locations don't have Internet access (other than satellite and that is still pricey). There is still a small niche for stand alone applications here but I'd be very surprised if that tiny market is ever dealt with in a meaningful way.

[orange]

Thanks for the

I suspect you design databases with grace and elegance and that they are things of efficiency and beauty.

, if only that were always true. I have had some successes.

I thought (perhaps mistakenly) that your requirement was triggered by a Washington State regulation.
It seems now that your issue is an inventory of "meat pieces" from the Farm, or similar.

What I'm trying to do is keep track of cuts of meat stored in different freezers and then in different containers in each freezer. So I've created a table with a PK, cut_code, cut_name, pkg_weight, price_lb, pkg_price, breed_name, freezer, location, date_processed.

For background and possible insight, you might spend a few minutes going through this thread re freezer samples. It's the thought process/evolution that may be helpful.