[RFC] Improve inventory management (long)

[mordante]

Introduction

This is a request for comment on the improvements I'd like to add the inventory management of Brewtarget. In short I'd like to be able to create a shopping list for one or more recipes and other items required. After buying the ingredients the amount in the inventory should be automatically updated. The goal of this RFC is to get feedback on the idea and possibly additional idea's. For example I originally wanted to buy for one recipe, but issue #188 already asks to buy for multiple recipes. It will be easier to implement these features when known upfront. (Just to be clear; I don't ask somebody to implement this feature, I just ask for feedback/hints. I will take care of the implementation myself.)

The proposal has two stages:

1. The cake: This makes the inventory management functional. It will be completely usable, but some things could be better.
2. The icing: This will make the inventory management even better. This part is a lot of work and I think having stage 1 is a huge improvement. So I'd like to finish that stage 1 first.

The noticeable missing part in this improvement is a way to show, print, or export your inventory. This is a feature I'm already working on and is rather straightforward.

The cake

User visible part

This stage involves the following features:

• Allows the user to select one (or more) recipes and add the recipes ingredients to a shopping list. It is also possible to add more recipes to the current shopping list.
• Have a shopping list dialogue. In this dialogue the user can add, remove, or modify the shopping list. The shopping list can contain:
  • Ingredients: fermentable, hop, miscellaneous, yeast.
  • Auxiliary items. Auxiliary items are a new type of item. This item are things needed for brewing, but not part of a recipe. For example: bottle caps, cleaning agents, new equipment, etc. Since you probably buy them at the same shop as your ingredients it is possible to add them to the shopping list. (This avoids writing them down on the printed list.)
• For ingredients the shopping list will suggest an amount to buy based on:
  • The current amount of the ingredient in your inventory.
  • The required amount to brew the selected recipes.
  • The wanted minimum amount in stock.
• It will be possible to change the suggested amount to the amount you want to buy. (It makes it possible to round up the suggested value to the lot size of the ingredient at your shop.)
• The shopping list can be printed, and exported to HTML.
• After buying the items it will be possible to mark the item as bought. This removes the item from the shopping list and adds the amount bought to the inventory.

  The implementation details

  Auxiliary table

There will be an auxiliary table with the following fields:

• id: The unique key.
• name: The name of the item.
• unit: An optional string containing the dimensions for the item. For example:
  • Bottle caps have no dimension.
  • A silicon hose will have a length dimension.
  • A cleaning agent has a mass, or volume, or no dimension. These units will not be part of the unit system.

    The ingredient inventory table

This table will get one extra field:

• minimum: The minimum amount of this to be in the inventory. The reason to put it in this table is for the second stage of the project. For the first stage this field will only be editable in the shopping list. In the second stage it will also be available in the ingredient's editor dialogue.

  The Shopping list table

This will be a new table with the following fields:

• idL The unique key.
• table: The foreign table we link to. This is one of:
  • fermentable
  • hops
  • miscellaneous
  • yeast
  • auxiliary
• key: The id of the record in table.
• required: The amount required for brewing.
• amount: The amount to buy.

When the user adds a recipe to the shopping list:

• It checks whether the ingredient is on the shopping list:
  • Yes: Add the amount required for the recipe to the required field.
  • No: Create a new record with the proper amount in the required field.
• Calculate the amount required:
  • amount' = required + minimum - inventory
  • If the amount' is greater than 0 the amount is not updated.
  • Else it sets amount to the maximum of amount and amount'.

If the user manually modifies required, minimum, or inventory the amount is updated with the same formula. It will never lower the amount field. It is possible the user already adjusted the amount to his/her wanted value. (When we add lot sizes in the second stage we can lower amount.)

In order to facilitate adding multiple recipes it still needs to add shopping list records which are completely in stock. After adding a second recipe the total amount required may be larger than the inventory. When the shopping list is printed or exported these 0 amount records are not shown.

When the user deletes an item from the shopping list the minimum in the inventory table will be set to 0. (This will not happen in stage 2. However in stage 1 the minimum acts like a part of the shopping list and not of the ingredient.) The minimum amount is still wanted in stage 1, since it makes sure it will not deplete certain ingredients. For example I like to keep a spare satchel of yeast, just in case my fermentation gets stuck. So if I happen to use the same yeast in a recipe I still want to buy a new one, despite having one in the fridge.

There will be a way to select multiple record and mark them as bought. This will:

• Add the amount in the record to inventory.
• Delete the record, which like the manual delete resets minimum field in the inventory.

The auxiliary table has no inventory and minimum. For shopping list items from this table only the amount field is available.

The icing

User visible part

This stage involves the following features:

• All ingredients get two extra fields:
  • Minimum amount to keep in the inventory.
  • Lot size. Like the inventory this is shared between copies of the ingredient. (There are/were some bugs in this area, like #72.)
• When the amount of the ingredient gets below the minimum amount it will automatically be added to the shopping list.
• The suggested amount to buy will always be a multiple of the lot size.

  The implementation details

  The ingredient inventory table

This table will get one extra field:

• lot_size: The ingredient will always be bought in a multiple of this value. (Obviously, unless zero.)

The fields minimum and lot_size will be shown in the appropriate editors. Editing the minimum, lot_size, or inventory in the ingredient editors will also update the amount in the shopping list, and when necessary add a record to the shopping list. For example, my fermentation gets stuck and I use my emergency yeast satchel. I update the inventory to 0. The minimum is 1 so a new shopping list record is created to buy 1 new satchel. (The lot_size is 0.)

The Shopping list table

The table does not change much. The editor will now also display the lot_size from the inventory table.

When the amount is determined its value will be rounded up to the next multiple of lot_size. It will now also lower the value. It is still possible to manually override the amount, but a recalculation may lower it again.

[mikfire]

The design looks pretty solid to me. I have just one minor nit.

The only thing I can really suggest is could we use a different name for the foreign key in the shopping list table? "key" is similar (in my mind at least) to "id" and it doesn't immediately suggest it is a foreign key. I would be happy even with something like fkey.

It is, as I said, a minor nit.

[mordante]

Thanks for the review. I agree with the naming. The current code uses foreign_key and foreign_table for these fields.

[pricelessbrewing]

@mordante Still up to doing this?

[mattiasmaahl]

@mordante You still want to implement this?

[matty0ung]

I think the underlying data model has moved on a bit since the above conversation. That said, the new database layer I'm soon (hopefully!) backporting from Brewken will make extending inventory functionality a bit easier, as it creates an Inventory class.

[mattiasmaahl]

That would be cool, the ideas in this issue/feature is intriguing. Would be a nice feature to have i think.