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

 

[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

 

[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.