Choosing your naming/coding convention for both incoming materials & ingredients and outgoing final product is actually extremely important. Get it right and it will ensure easy, consistent and predictable database management, get it wrong and it may cost you dearly. All too often I see companies operating sub optimal conventions which I have no doubt costs them time, money and adds unnecessary complexity and therefore risk to their operations.
The following are just a few of the more important guidelines, and rules of thumb that you need to follow when choosing the naming/coding convention for your business. It doesn't matter if your database management system is the back of a cigarette pack, an Access database, or SAP – these rules hold true regardless.
- You do need at a minimum two different tables/lists/registers (whatever you want to call it). The first is for incoming ingredients, components and materials that you purchase from your suppliers, and the other is for final products that you sell to your customers (let’s collectively call them ‘entities’ from now on). It doesn't matter that you may do little to no processing on the incoming good before you sell it on to your customer. It could be the exact same untouched ingredient but because it has passed through your operation it is in effect now a product (with perhaps just one ingredient of the same name) so it needs to be cataloged differently.
- Likewise if you purchase the exact same ingredient from multiple different suppliers (example ‘Sugar’) then these are different ingredients requiring different codes. This is critical for a multitude of reasons including traceability, purchasing, bill of materials, supplier control, specification revision and risk assessment (country of origin etc). If you have one code in your database for Sugar and you buy it from multiple different suppliers then the very foundation of your database is compromised.
- At an absolute minimum your table will have three columns or fields, whether we are talking about products or ingredients. The first is the Code, the second is the Name and the third is the Description. Don’t confuse a product name with its description, they are two different things. The name of your product should be as close as possible to the name that is used when sold to your customers. The name of your ingredient/material should be exactly as it is named by your supplier. If you are not happy with the name your supplier has given (perhaps it is too cryptic) then ask them to alter it sufficiently so that it works for you. The point here is consistency through your internal supply chain and the various different systems it will pass through.
- Never delete or recycle entities. Once you have created a product or ingredient in your database it should never ever be deleted or reused for something else. You can add a column to your table called Archived or InUse and simply mark each entity 1 or 0 to designate it as in use or retired. This will hide the entity from the lists and prevent it being used accidentally but still allows record retrieval in the future when it is required.
This can be the hardest one to get right but is possibly the most important. When your staff can just as easily recite the code of your products/ingredients as their respective names you know you are doing something right. This is especially important when you produce products or purchase goods that are very similar and differ in only minor characteristics.
The coding mechanism employed will often be determined by your DBMS (Database Management System) but with complete freedom of choice you should opt for an alphanumeric code which includes two letters followed by at most three numbers, giving a code length of 5 characters max (e.g. BY001). If you think you can get away with just 2 numbers bringing the code length to just 4 characters then all the better. The letters are the most important part and they should have ‘meaning’, i.e. they should reference something in your operation. It could be a brand, a product line (most common), a production area, a market or even the season of the year. You should choose carefully here but in nearly all cases the choice will be obvious.
As for the numbers that follow, stick to sequential numbering. Don’t try to be too clever and start numbering different sub-product lines at different stages (e.g. BY100, BY300, BY500) no matter how tempting that may be. It is only a matter of time before you run out of numbers and this breaks down leaving you having to re-use or delete existing items which is a cardinal sin, or even worse having to skip a whole bunch of numbers into a different section. Many companies do this, but it is not recommended.
It is also possible to use the products UPC code which very often is the unique identifier your database uses but these are impossible to remember by people. Hence why the vast majority of businesses will operate their own internal coding system regardless.
As stated already consistency is essential in both coding and naming. When naming your products you should strive to name them exactly as they appear on the invoice to your customers. Just like coding you also need a naming ‘convention’ which you stick to no matter what. The most common approach is to include at most 3 characteristics into the name, starting with the Unit of measurement sold. E.g. 500ml XXX or 360g YYY or 7oz ZZZ. This can be followed by the next most important attribute such as the Product Line, the Brand or even the Customer. Finally, the third characteristic is the variation of this product which could simply be a pack size, a color, a particular wrap etc. Here are some examples of products and their codes (not real, just made them up);
- BV016 – 500ml Forest Mist Spring Water Shelf-ready
- BV019 – 500ml Forest Mist Spring Water 12-Pack
- AA369 - 14oz Welsh Cottage Pie RTE
- AA375 - 28oz Welsh Cottage Pie Promotional
Ingredients and materials are the raw components you purchase from your suppliers which go into making up your final product. You have complete control over the coding mechanism used but less so over the naming. As already stipulated you should try to use the same name as that which your supplier uses. You may, if you wish, embed the supplier’s own code right into the name, being the only thing you should change. This is particularly useful if you buy the same ingredient or material from several different suppliers however you should not rely on this solely to distinguish between them. Ultimately you will have assigned each an internal code which will be intrinsically linked to the respective supplier and no matter what IT system you use you should be able to quickly and easily identify that relation.
The above are my own best practice guidelines based on nearly 15 years experience working with many different food businesses and database systems. If you are currently doing things differently, and it works well, then there is no need to change it!
Coming to the OP’s question. I would certainly not recommend including allergen information in the name of either incoming ingredients or final products. To do so on its own sets a weak and unconventional precedent which if not followed to the letter can introduce risk and could result in a recall. When purchasing ingredients and materials they should be risk assessed for hazards as part of your supplier approval program and assigned a risk rating, along with an associated list of hazards including allergens. How they are managed from this point through your operation is a whole different discussion and one we’ll leave for another day.
Hope this helps OP. Let me know if you have further questions.