Question How can I set up different batch sizes for recipes with the same name?

[PamelaJoy]

I am working on a db for a chemical manufacturing business. We primarily want to be able to determine the cost of each recipe, and determine which finished products contain any given raw material (so that when there is a price increase, we can determine which products will be affected). We will also run tons of query based reports.

Each finished product has its own recipe. Each recipe has four possible batch sizes (5, 55, 275 gal or 45,000 lb tanker, corresponding to four container sizes).

How would I best keep four separate recipes (the amount of each ingredient changes based on batch size) but not require four separate finished product and/or recipe names?

Here are the tables I have set up:

tblFinishedProduct
FinishedProductID (PK)
FinishedProductName
FinishedProductCategoryID (precipitant, coagulant, etc)

tblRecipe
RecipeID (PK)
FinishedProductID (FK)
BatchSizeID (FK) (# gallons finished product, also container size)
RawMaterialID (FK)
RawMaterialSequence (order of ingredient)
RawMaterialQuantity (in pounds or gallons)

tblRawMaterial
RawMaterialID (PK)
RawMaterialName
PerUnitCost
PerUnitFreightCost
UnitofMeasureID (FK)
SupplierID (FK)

tblCategory
CategoryID (PK)
CategoryName

tblBatchSize
BatchSizeID (PK) (and FK to Container size)
BatchSize

tblUnitofMeasure
UnitofMeasureID (PK)
UnitofMeasure (pounds or gallons)

tblContainer
ContainerID (PK)
ContainerSize (FK to BatchSizeID)
ContainerWeight
ContainerCost
SupplierID (FK)

tblSupplier
SupplierID (PK)
SupplierName, SupplierAddr, City, Contact, etc.

tblFinishedProductDetails
FinishedProductDetailsID (PK)
FinishedProductID (FK)
FinishedProductCorrosivity, Weight, Density, SpecificGravity, pH, etc.


Thanks,
Pam

 

[Pat Hartman]

Welcome aboard. I worked on this exact application for Clarol. We were manufacturing shampoo and other hair care products. The ingredients were measured by weight whether they were solid or liquid and the formula (recipe) ingredients were stored by percentage. Everything was kept at a precision of 6 decimal places. Dye/scent packs represented too small a percentage of the total batch size to use the same precision so they used 6 decimal places but they were stored as 100 times their actual value so to avoid .00000105 from being truncated to .000001 it was stored as .000105. The dyes/scents were all weighed outside the batching process and added as a total amount.

To calculate the quantities for an actual batch, you would multiply the batch weight by each ingredient's percentage of the total weight.

When you use a PK as a FK in a related table, use the PK name. That way there is no confusion as to what table is being referenced.

I should point out that the bill of material, which is the "master" of the recipe could vary from the recipe since the recipe was defined in the order in which items were added to the mix and if an item, such as water, was added at different stages or different tempatures, it could be listed multiple times in the recipe even though it would appear only once in the BOM.

 

[Watergirl]

Using the percentage of the total weight is a great idea - thank you. I will correct the FK name as well, good point.

I think I will remove the product sequence from the recipe table altogether and put those details in the finished product details table. (It is how a 'real' recipe would read in the kitchen, too!)

Since you are so familiar with the application, and this is my first database attempt, do you have any tricks, tips, or see any potential problems or design flaws with what I have planned? I don't want to go any further (reports, forms, etc.) until the foundation is solid. Thank you for taking the time to respond to my initial question. Pam

 

[Pat Hartman]

In a manufacturing system, the bill of material is separate from the formula/recipe. You refer to the recipe but don't mention the BOM. Will this application be used to create shop floor work orders to schedule batches? In my WeightMaster application for Clarol, the purpose of the app was to create directions for creating batches. It took the requested batch size and forumla and applied it to the percentages in the BOM to create a sequenced materials list with the weights for the specific batch. It then merged the calculated figures into the "recipe" document which included mixing instructions such as speed, time, and tempature.

 

[Watergirl]

We're not that sophisticated - we manufacture wastewater treatment chemical compounds and each batch simply needs to come within a certain pH range. Batch testing is done when a batch is made, whether a tanker or a 55 gallon drum. If necessary, minor adjustments (adding a little acid or caustic to meet spec, for example) can be made at that time, and the final results recorded (maintained in a standalone Excel spreadsheet).
The primary reason for the creation of this database is to enable us to plug in the current price of the raw materials in order to then determine our cost of each finished product, thus, a recipe. We currently have a crippled dBase system whose author passed away over ten years ago - no documentation and no one here knows how to fix or update the system. Manual adjustments have to be made every time we update the raw material pricing (sometimes monthly, most often quarterly) as it's no longer completely reliable. Orders are placed through a Peachtree Accounting system, where our current cost is captured (based on the data we feed it from our dBase report).
Being able to print recipes is secondary, but will certainly be beneficial. We currently have printed recipes for every product in a 55 gallon batch size. Production has made notes on how to make 5 gallon, 275 gallon, or 45,000 tanker batches (the only batch sizes we sell). There are no notes as to order of addition, mixing sequence, how long to mix before adding the next ingredient, etc. which I would like to add eventually.
There are only four of us - the GM, two guys in production, and myself (admin assistant/receptionist/accounts payable/receivables etc.) at the office. I was a programmer (Cobol, ALC) some 25 years ago - so we 'assumed' that my getting through 'The Access 2002 Bible' to teach myself enough to get this database up and running would not be that hard.
Ha! I have been at this for a year (during slow times in the office, some nights and weekends) and am just now beginning to see light at the end of the tunnel.