I work as the R&D manager for a small ink company and I want to improve our formula (recipe) system, which is currently very poorly structured. I have some experience with databases (having made some php/mysql applications and having worked for a few years with SAP-ERP, Access and VBA).
I'm at a bit of a loss as to how I can make a properly structured database that will work with formulations (without having a lot of blank spaces), so any suggestions, even to which subforum I should be asking, would be appreciated.
Final note, the company I work for is reluctant to purchase a formula or recipe management system, so it seems like the only way I will get a better system is by building it myself.
To give a little more information about the database I want to make:
1) A "formula" has some metadata (who created it, when it was created, if it is a clone of another formula and what changes were made if it was a clone) but is primarily a list of chemicals (10-20 out of hundreds of chemicals that we have on hand) with the amount of each given.
2) A "chemical" is one of a set of different types of chemicals and should contain other information, such as common name, % solids, etc.
3) A "chemical lot" is a child of a "chemical" that has specific information about that barrel or container of the raw material, such as measure specs, date of receipt, etc.
4) A "formula batch" or lot is a child of the formula that will have test results, measurements of viscosity, surface tension, etc. It should also link to the lot of each given chemical, and include the scaling factor for the total batch size.
With our current Excel-based system this ends up with huge documents with rows for each chemical, and columns for each formula. Formula batches are worksheets that are copies of the formula sheet with non-relevant formulas deleted out and unused chemicals hidden (or deleted). This means that 97+% of the formulation sheet is blank (or zeroes) and when a new chemical is added it adds another row of zeroes to everything that has ever been tested.