Database Schema

[matty0ung]

This may be a stupid question, but can anyone tell me what the bt_xxx tables are for?

[mikfire]

Sure. They allow us to find our data when we want to update an ingredient or something. As an example, we ship 14 water profiles by default right now. Let's say the user adds 6 profiles, and then we update our database and add a new profile. The entry in bt_water will be (id,water_id) if (15,21). On the next update if we have to fix that last water profile, we can easily find it in the user's database.

They were introduced in 9838ed2. If you dig into Database::updateDatabase, there is a description of the mechanism but not much else.

I thought we had some basic docs on how to use them, as I need to remember how this works every time I look at it. Apparently, we don't. I should probably fix that.

[matty0ung]

Ah, I see, yes that makes sense now. It's like an extra optional ID for the items of data that are shipped with the program.

What happens in the (rare?) event that the user has edited, say, the supplied water profile that we want to fix? Do we (can we) detect that, or would their changes by lost by the merge? (Presumably this is why there's a pop-up warning before we do such merges.)

[mikfire]

First answers first. To my knowledge, we haven't ever shipped a 'correction' to something in the database. We have added a few new things, like the water profiles and some hops. But I do not believe we've actually corrected something. I wouldn't ever trust my memory. You could do a git blame on the default_db.sqlite to see how many times it's been changed.

Oh man. Rethinking the schema is a can of worms, and one I've looked at a few times.

There's a lot of history here. We originally stuck to the beerXML spec because rocketman required it. He made a convincing argument as to why it should be that way. I vaguely remember it had something to do with backwards compatibility with brewtarget 1.x. It's been long enough that I only remember it was a fair argument. He was also the project owner, he said those were the rules and so I either played by them or forked off.

I have made my opinion about beerxml painfully clear (it sucks). I dislike being tied to a poorly written spec that is 20+ years old and no longer reflects modern home brew practices. I think you can see that I have no issues extending it when I like to, like water. My opinion is that we only need to be able to accurately import from and export to beerXML. As long as we translate all the things properly, we can do what ever we like in the database.

Having said that. I am still scarred from the 1.x -> 2.0 transition. It took rocketman and I about a year. We worked on it every night after the day job, and I spent a lot of weekend days on it too. I am unwilling to take on that kind of work without seeing significant improvements to both using brewtarget and maintaining the code.

Just a thing to consider. Brewing software is trying to solve three different problem domains at the same time. The first problem domain is recipe formulation: build a recipe, doing the math to calculate IBUs, ABV, etc. The second domain is the brew day: heat X amount of water to Y temperature and hold for Z minutes, as well as brew notes. The third domain is inventory: how much of ingredient X do I have on hand, and when does it expire.

What people want from brewtarget depends on how interested they are in each domain. I am deeply interested in the first, somewhat in the second and not at all in the third. That may have been obvious based on how I laid them out 😀 And just because somebody else is far more interested in the third domain than the first doesn't mean anybody is doing it wrong. It just means I will have a harder time understanding the problems in that domain and then solving them.

Let me poke at your idea for a bit. I am not the sharpest rock in the drawer, and I have found I usually do better for chewing on an idea for a while before reacting.

[matty0ung]

I hear what you're saying. I agree it's not worth doing a big change if it doesn't bring a big benefit.

I know there are some things that deeply interest me as a microbrewer but which don't belong in homebrew software - eg, precise tracking of what's been brewed so I can account for alcohol duty. (Literally every time I open a bottle, even for myself, I need to make a note so that it can be included on the monthly Customs return.) One of my long-term projects is to make a downstream project which uses the recipe-formulation and brew-day stuff from Brewtarget but then adds microbrewery-specific functionality (sales, batch tracking for quality control, duty tracking, expiry dates, cost tracking, etc). Probably going to take a few years, but I hope it will ultimately replace a lot of complicated spreadsheets and Word documents that I use at the moment.

[mikfire]

Based on what I am fixing in other places, I am removing the code that updates existing rows in the database.

Once we ship the data to the user, it isn't ours to touch any more. I would be furious if somebody just blithely over wrote my changes with their data. So we aren't doing that any more. It makes updateDatabase() a bit easier to work through.

[matty0ung]

That sounds like a good call. It feels like, if we have new data to offer to the user, it should be their active choice to import it and, like you say, we shouldn't overwrite any of their existing data.

[mikfire]

Actually, we do have one problem.

The BJCP and Brewers Association frequently update their style guides. We will need to figure out some way of updating those. I can see a few ideas, but it will require some thought.

[matty0ung]

Another quick question, where does the recipe_children table get used? (I understand that hop_children, fermentable_children, etc get used when we create a copy of a hop, fermentable, etc to add to a recipe, but since we don't add recipes to recipes, I started wondering about recipe_children.)

[mikfire]

I once had a beautiful system for automatic versioning of a recipe. The PR never got merged because I couldn't quite figure out what "delete" meant.

I also think I spend more time writing and tinkering with recipes than other people, so the idea of an automatic versioning system never really made sense to anybody other than me.

I believe the recipe_children table to be a left over from that effort, and can likely be dropped.