Bakehouse Diary

Written by Piers Cawley on

Week two – SQL

Not much progress on the “getting the oven ready front this week”. I sheared another machine screw!

At least this one was on the retaining bar that holds the oven soles in place; I’d rather not have sheared it, but since I’m going to be drilling stuff out anyway, it’s not the end of the world. Basically I’m blocked on doing much more with the oven ’til I’ve moved the decks and got them the right way up rather than on their sides because I don’t want to undo the last screw in the retaining bar and cope with a couple of large lumps of metal and (possibly) the insulation and heating elements falling out. Once the ovens are the right way up, gravity will be my friend.

So, I’m going to talk about the software side of things.

Pricing the product

Last year, I went up to Bread Matters in the Scottish Borders and did their excellent Baking for a Living four day course. I can’t recommend it highly enough; if you’re remotely interested in taking the next step in your baking and going pro, then this will give you a solid grounding in stuff you might not have thought about.

One of the exercises was to work out the costings for a product. If you follow the rule of thumb that’s often bandied about for restaurants and catering (multiply the ingredient costs by 3 and you’ve got the final price) you are almost certain to go bust. The working formula is more along the lines of:

[\begin{eqnarray*} Price_{wholesale} & = & \sum\nolimits_{i \in \lbrace ingredients, labour, package, transport \rbrace}\frac{cost_i}{gross\_margin} \\

Price_{RRP} & = & (1 + retail_markup) \times Price_{wholesale} \end{eqnarray*}]

With rules of thumb like $cost_{transport} = 0.1 \times Price_{wholesale}$1 and $cost_{labour} \approx 1.1 \times cost_{ingredients}$, which can be replaced with $cost_{labour} = hourly\_rate \div production\_rate $ once you know how many loaves you can make with an hour of work.2

Working all this stuff out is a complete pain in the arse. It’s not the sort of thing you want to be doing every time you change a product formula, or you negotiate a wholesale rate with your miller, or the pound goes through the floor and suddenly you’re paying 30% more for your dried fruit.

On the course, after we’d done the calculations by hand, for one product, we were given a spreadsheet, which was great so long as you were plugging in new numbers for ingredient costs or your hourly rate. But it’s in the nature of spreadsheets that, once you want to start adding a new product, there was an awful lot of copying and pasting of formulae and general faffing about.

If there’s one thing my time as a programmer has taught me, it’s that faffing about is the sort of thing you should really be leaving to the computer.

So, once I got back from the course (and on and off over the nine months since), I set about converting the spreadsheets to a relational database. The great thing about using a database for this sort of thing is that, if you set up your tables and views correctly, you only have to type formulae like the one for the wholesale price in once and after that the whole thing becomes data driven.

If you’re happy with absolutely zero documentation and a simple SQL command line, then you can take a look at my progress by looking at my bakehouse project on Github.

Ingredients, Recipes and Products

If you have a collection of bread books, you’ll find that the ones aimed at the home baker often have a slightly different dough recipe for every kind of loaf you might want to make. Which is fine when, in the course of the day you’ll be baking one loaf (or batch of buns or whatever). When you’re baking commercially, this doesn’t really fly. You may be making 10 or more different products and and if you can make three different products (say large loaves, small loaves, and baps) from a single huge lump of dough, then it makes sense to do that. And if you can carve off big lump of that dough before the bulk ferment and add some dried fruit to so you can make teacakes as well, then that makes sense too. In fact, if you look carefully at the proportions in the average domestic bread cookery book, you can probably pull out a few common doughs which are then jazzed up with other ingredients to make most of the things in the book.

A commercial formula for a product will almost certainly be multi stage - First you take some starter from yesterday and feed it with flour and water to make a production leaven, then after four hours you take some of the production leaven, flour, water and salt and make up your basic doughs (white, wholemeal, granary) which you’ll leave for their bulk ferment before scaling, shaping and leaving to prove (overnight in the retarder unless you want to be baking at 3 in the morning) so you can come in in the morning, fire up the ovens, bake off the buns, then the loaves, then the fancy breads and ship everything out to your customers. Fancier products might involve more fiddling about. Stollen is a classic ‘complicated’ multi-stage recipe which involves making up:

  • Marzipan - made the day before
  • A fruit soaker - dried fruit and booze or apple juice, made the day before to get the fruit good and plump.
  • A ‘flying ferment’ of flour, milk, sugar and yeast. Because the final dough is quite heavily enriched and full of fruit, you want your yeast to be good and active. This gets made a couple of hours before mixing the dough properly.

Then you make up your Stollen dough with the ferment, eggs, more flour, more milk and work in some butter too, which gives you a lovely soft rich dough. After an hour or so, you mix in the fruit, let it relax, then scale up the dough and the marzipan and make up your stollen, prove, bake, slather with melted butter and try to resist scoffing all at once.

If I were simply after working out the ingredient cost per loaf, I could represent this recipe in the database with a simple table like so:

StollenGround almonds0.060
StollenCaster Sugar0.020
StollenIcing Sugar0.020
StollenWhole egg0.020
StollenFresh yeast0.005
StollenWhite flour0.050
StollenWhite flour0.110
StollenWhole egg0.050
StollenSalted butter0.050
StollenCandied mixed peel0.050

but that throws away a bunch of information and is a surprising pain in the arse to type. It’s also very hard to look at that table and deduce anything about what needs doing when in the stollen making process. So we write the table a little more naturally.

The recipe_ingredient table

StollenStollen Dough0.560
StollenBeaten Egg0.010
StollenIcing Sugar0.010
Stollen DoughStollen Fruits0.200
Stollen DoughFestive Bread Dough0.360
Stollen FruitsSultanas0.070
Stollen FruitsRaisins0.060
Stollen FruitsCandied Mixed Peel0.050
Stollen FruitsRum0.020
MarzipanGround Almonds0.060
Festive Bread DoughWhite Flour0.110
Festive Bread DoughFestive Ferment0.120
Festive FermentSugar0.005
Festive FermentFresh Yeast0.005
Festive FermentMilk0.060
Festive FermentWhite Flour0.050

The product table

If we want to calculate an accurate price, we also need to capture the scale weight of a ‘piece’ of product, the number of pieces we can make per hour of labour, and (think of batched buns, for instance) the number of pieces that go to make up a product. This gives us a table like:


Breaking down the price

So, now we just need to calculate the cost of all the ingredients in a loaf which we can use as the inputs to the formulae above. At this point, I’m just grateful to be a programmer because the SQL query that does that calculation is… complicated. The spreadsheet version of the calculation copes with the arbitrarily nested nature of a recipe linking formulae together by hand (which is part of why the spreadsheet version is hard to extend), the Postgres version it ends up with a rather icky recursive query which calculates the per kilo price of each product and intermediate recipe by starting with a table of just the raw ingredient unit prices and at each iteration calculates the unit price of all the recipes which can be made using the ingredients and intermediates we already know the price of. If you want the gory details, check out unit_cost.sql.3

Next feature

So, that’s the price breakdown sorted, so now I have some numbers to plug into a business plan and, indeed to stick on a pricelist. Huzzah. What’s next?

Here’s a question I want the software to answer: say I arrive at the bakehouse to start the day’s work. I’ve got orders for a couple of dozen large sourdough loaves, a dozen large wholemeal loaves, four dozen burger buns and a pile of cheese straws. What do I do first?

This is where the order and production_sheet tables come in. The order sheet is pretty straightforward. Two columns, product and quantity (I might get fancy and and add a third column for client, but that’s for later). The production sheet less so.

Right now I know how to make a view which has recipe, ingredient and quantity columns, the thing that’s currently puzzling me is how to add a ’time’ column so I can have a view like:

t-15h100% SpongeWhite flour5kg
t-15h100% SpongeWater5kg
t-15h100% SpongeFresh Yeast25g
t-3hBasic White Dough100% Sponge10.025kg
t-3hBasic White DoughWhite Flour5kg
t-3hBasic White DoughSalt200g
t-3hBasic White DoughWater1.6kg
t-1h30mScale18xLarge White800g
t-1h30mScale6xSmall White400g
tBakeLarge White
tBakeSmall White
t+40mPullSmall White
t+50mPullLarge White

That needs a better representation of a recipe in a database. One which captures details of how long each step takes and what resources are needed while it’s taking place. Watch this space.

I’d really like to produce a schedule for multiple potentially overlapping products being prepped at once and constrained by available labour (only one activity can be carried out at once because I’m my only employee) and retarder and oven space. Unless I’m missing something that’s a proper NP-complete problem and that’ll have me hitting Knuth’s preprints on SAT solvers. Which will be fun.

Next week

Most of the people who can help me getting the oven on its feet are going to be away next week, so I doubt there’ll be much progress on oven commissioning, but I hope I’ll be able to keep making progress on the bakehouse software.

  1. You will note that the transport cost is defined as a fraction of the wholesale price, but the wholesale price is defined in terms of the raw cost of the product, which includes the transport cost.

    At this point, it’s good to remember your algebra. After a certain amount of fiddling, it’s possible to define the transport cost solely in terms of the other input costs and the desired markup. It comes out as $$\frac{\sum\nolimits_{i \in \lbrace ingredients, labour, packaging \rbrace} cost_{i}}{1 - transport\_allowance\_rate - gross\_margin}$$ which which can get entertainingly huge as $transport\_allowance\_rate + gross\_margin$ approaches 1. So we make sure they don’t. ↩︎

  2. Which can be a bit of a pain in the arse to calculate. You should only really count the time you spend actively working on the components of a product and not the time you spend waiting around for dough to prove or bake or whatever. It’s worth doing though. Accurate numbers matter. ↩︎

  3. Bear in mind that the table structure in the app isn’t quite the same as the tables described here. ↩︎

  • 0 replies
  • 0 mentions