In short
A formula record stays correct for years only if each fact is stored once, in the right place, under an identifier that never changes. That means separate tables for materials, stocks (a material at a strength in a solvent), formulas, formula lines and versions, joined by stable ids rather than names or CAS numbers. With provenance recorded beside each value, any formula can be recalculated, audited or shared without guessing what it meant.
What you will learn
- Separate materials, stocks, formulas, formula lines and versions into tables with stable keys
- Explain why a CAS number or a name is an attribute and not a key, with real cases
- Record provenance, units and dilution so a formula can be recalculated years later
- Apply the FAIR principles to a perfumer's own research records
Before this lesson: Documenting a Formula: Study Guide, Formula Versioning: Study Guide, Recording and Sharing Data
Before you start
This builds on the formula template, version control and recording experiment data. No programming is needed; a spreadsheet with several sheets is enough.
The five tables
| Table | Key | Holds |
|---|---|---|
| materials | material_id | name, alternative names, type, CAS (if any), supplier, grade, lot, a recorded limit with its source and date, cost per kg with date |
| stocks | stock_id | material_id, solvent material_id, strength %, date made, made by |
| formulas | formula_id | title, purpose, owner, status |
| versions | version_id | formula_id, version number, parent version, date, reason for change |
| lines | (version_id, line_no) | stock_id or material_id, weight in grams, a note |
A formula's numbers are never stored; they are calculated from lines, stocks and materials. A stored "relative %" goes stale the moment a dilution is corrected. This is the same rule the RUŌOD Lab app follows for usage counts, and the reason is the same: a derived value cannot contradict its sources if it is never written down.
Why names and CAS numbers are not keys
Names change, collide and are misspelled. CAS numbers are better identifiers of substances, but they do not identify products. This site's own catalog has the cases: Kharismal is sold under Hedione's CAS number and is kept as a separate record because its grade is undocumented; Norlimbanol shares Timberol's CAS number but is a different isomer mixture. Many bases and naturals have no CAS number at all. Use your own id as the key, and record CAS as an attribute, with its source.
Units, dilution and provenance
- Units are explicit. One column, one unit, named in the data dictionary: grams, never "parts" in one row and grams in the next.
- Dilution is data, not text. "Ambroxan 10%" in a name is invisible to calculation; a stock row with strength 10 is not.
- Every external value carries its source and date: a limit from a named IFRA Standard amendment, a cost from an invoice, a CAS from a certificate.
FAIR, for one perfumer
The FAIR principles were written for research data. For a perfumer's own records they translate directly: findable (ids and a consistent folder and file naming), accessible (formats you can open in ten years, such as CSV), interoperable (columns defined in a data dictionary, units stated) and reusable (provenance and a statement of what may be shared). Formulas are often confidential; FAIR does not require publication, only that the data be usable by whoever is entitled to them.
Where it stops
- Naturals are mixtures. A constituent table (material, constituent, typical %) is needed for allergen or restriction arithmetic; it multiplies the model's size.
- Some facts are judgments. "Grade unknown" is a valid value. Recording uncertainty is better than inventing precision, as this catalog's reference entries do.
Practical exercise: Normalize your own library into five tables
- Create five sheets or CSV files as above. Add at least ten materials, with an id scheme you will never change.
- Add the stocks you actually keep, each referencing its material and solvent by id.
- Enter one formula in two versions, the second changing one weight and one dilution, with the reason recorded.
- Write a data dictionary: every column, its type, its unit and what a blank means.
- Calculate one version's pure composition from the tables alone, with no number typed by hand.
You have done this well if no fact appears twice, every reference is by id, every external value has a source and date, and someone given only your files and dictionary could recalculate the formula and say what changed between versions.
Common mistakes
- Keying on names. One rename and half the formulas point nowhere.
- Storing calculated percentages. They go stale silently.
- Overwriting a version. Make a new one; the history is the record.
Key terms
- Primary key
- A column whose value identifies exactly one row and never changes, such as a material id. Other tables refer to the row through it.
- Normalization
- Storing each fact once, in the table it belongs to, so that changing it in one place cannot leave a contradiction elsewhere.
- Provenance
- The record of where a value came from: a supplier's certificate, a paper, a measurement, a decision. It is what makes a value checkable later.
- FAIR principles
- Guidance that research data be Findable, Accessible, Interoperable and Reusable, with emphasis on machines being able to find and use the data.
Knowledge check
Written and reviewed by the RUŌOD Lab team. This is general education, not legal, regulatory, medical or safety advice. Example dilutions and exercises are for learning, are not validated products, and have not been safety-assessed. Completing a lesson confers no qualification. How we check these lessons.