Jump to content
Mr.Beer Community
MiniYoda

Creating a recipe database

Recommended Posts

I'm still slightly overwhelmed as to how many recipes there are just using Mr. Beer products.  I've been asking myself, okay, what do I order next.  I thought, wouldn't it be helpful to have a searchable database, so that I could say "show me all recipes that use the Oktoberfest, and let me know how long each take", or show me all recipes that use have pumpkin and have a ABV above 5".

 

I'm an IT geek, part server admin, part programmer, part DBA.  I knew putting all these recipes in a database would be perfect to store and search.  But as with anything, baby steps.

 

Attached is MrBeerRecipes version 0.1.  It's a spreadsheet right now, not a database.  It's more of a starting point where people can submit suggestions on what to add, and how to format the fields.  Once I have all their recipes in, both current ones and archived, I will explore a way to put the file on the cloud for all to use.  I will then research a way to create a database where people can submit their own recipes.  I'm opening the floor right now to suggestions.  As I type this, I just thought of another field to add:  Seasonal (Christmas, fall, etc)

 

MiniYoda

MrBeerRecipes.xlsx

Share this post


Link to post
Share on other sites

Hi, It seems some of that searching is on this page,  you can select the HME, and a whole range of parameters to filter by and it works pretty well

What would you say it is missing? 

I would say though it does need some update, there is only 1 Partial Mash recipe in there.

 

But assuming it was updated, then what else?  Other filters?

 

http://www.mrbeer.com/refills/recipes

 

Share this post


Link to post
Share on other sites

Yes, for sure the archived, I think they are just moved to make the top search smaller. They do seem still to be for sale in most cases.

Even when out of stock it is nice to have them there so that recipes can be recreated even if Mr B does not have all the ingredients packageable.

I have been keeping my own recipe archive which includes some that are not available and not in the archived list either - if there is something I think looks good.

e.g Lucky Charms Milk Stout.

However, a bunch of the recipes are strangely here on this page e.g.

http://support.mrbeer.com/support/solutions/articles/5000623207-cerealiously-lucky-charms-milk-stout-recipe-instructions

 

A search for "recipe instructions" seems to find them all.

http://support.mrbeer.com/support/search/solutions?term=recipe+instructions&authenticity_token=4Eqd2DgrjWtRLLlzgLlWMqZsoABuyA0FtbwXKz8LXKQ%3D

 

So here is more data you can collect before they change the web site  :lol:

 

There is a lot of overlap but quite a few are not in the main page.

Share this post


Link to post
Share on other sites

no, it's more data for all of us to collect before they change the web site.

 

A long time ago in a job far away (sorry.....had to), I was a web/db programmer.  While the way to do web pages and database have changed, the method to get there stays the same:

 

1) design the database.  what fields (items) each record (recipe) should have

2) implement or update a spreadsheet to hold this info

3) add records

4) repeat steps 1-3 for a while

5) design a web page (form) where people can enter search criteria.

6) write code to send information from page to a DB server

7) write code for the DB server to return the information

8) design a web page to display the results

 

All of these, to me, are very easy...I did this years ago using very simple web and database skills.  Things have changed over the years, so I'll have to work to make things better, but........baby steps.  First step is to design the database to hold the info needed, and add data to make sure all test cases work properly.  I am going to spend the next week or two adding data to the spreadsheet.  If folks can look at it and submit suggestions, please feel free to do so.  Not recipes yet.......just the pieces parts of a recipe.

 

The next big phase will be to convert the spreadsheet to a web form/results page.  While I can do this, I might talk to some friends who know more advanced methods.  I will also have to work with the Mr. B team to make sure that I can find a cloud site that will host the database and web server, and they can provide the link to this site for recipe searching.

 

Down the road, once we have something working, we can have a form where people can enter information to submit a recipe, such as the lucky charms milk stout.  Information about temperature ranges, ABV, conditioning times, can be recorded on each recipe.  One of my fields for each record is who submitted the recipe, so they (eventually) won't be all Mr. B's.....but of course, Mr. B's approved.  There will be methods to ensure that only certain people can approve and/or alter information in the database. 

 

It's a project, but I have the time on my hands now that SWMBO is now She Who Filed For Divorce.  I'm going to work on adding recipes and updating the design of the spreadsheet, and welcome ideas on the spreadsheet itself.  I'll submit updates from time-to-time.  Once we have something that looks good, I'll design simple web forms and a database, and find a site to host it.  Then, once we get a method to the madness, we'll add a method to add recipes.

Share this post


Link to post
Share on other sites
12 minutes ago, RickBeer said:

Wasn't saying to add them.  The 2012 and prior are obsolete.  The Wayback Machine allows you to find any recipe Mr. Beer had.  That's all.

 

I can't get to the Wayback Machine from the office due to a security connection failed.  I'll have to check it out when I get back home. 

 

My intent is more than just finding a recipe.  My hope is that this will allow to search on multiple criteria and allow people to submit their own recipes

Share this post


Link to post
Share on other sites
9 minutes ago, RickBeer said:

Understand.  I would suggest that you want to differentiate between a Mr. Beer created recipe and a customer created recipe though.

 

My current revision of the spreadsheet has a field for who submitted the recipe.  As of now, there are 152 records of Mr. Beer's recipes, but LOTS of details to type in.  I'll upload the latest version later today.

Share this post


Link to post
Share on other sites

I am totally happy with just the spreadsheet. Probably database on web page is more generally accessible, but spreadsheet captures data and is OK for now.  Preserving the data is first for me, making it pretty is 2nd.

 

Obsolete recipes often use obsolete HME can styles so not useful. Same with some Mr Beer Seasonal cans, now not available. But Substiture recipes for the unavailable HME cans would be nice.

 

You could also add a rating system for "likes" on recipes ?  That would let people sort on popularity too.

 

Share this post


Link to post
Share on other sites
7 minutes ago, Nickfixit said:

I am totally happy with just the spreadsheet. Probably database on web page is more generally accessible, but spreadsheet captures data and is OK for now.  Preserving the data is first for me, making it pretty is 2nd.

 

Obsolete recipes often use obsolete HME can styles so not useful. Same with some Mr Beer Seasonal cans, now not available. But Substiture recipes for the unavailable HME cans would be nice.

 

You could also add a rating system for "likes" on recipes ?  That would let people sort on popularity too.

 

 

I'm going to avoid the Obsolete recipes, since my column for HME will reference current products.  Perhaps someday Mr. Beer can find a way to bring obsolete recipes back to life with current HMEs.  I am adding the Archived ones, though, even if they are listed as "sold out".

 

I thought about the rating system, but that would be a later phase if/when the database comes online. There's no way to do a live rating system on a stagnant spreadsheet.  Once we get it live, we can do a rating, but I need to make sure one person can't do the rating more than once.

 

Quick question.  I have a field for ABV, but should I include fields for OG and FG?  Now is the time to add fields, before too many records are entered and I have to go back to find info

Share this post


Link to post
Share on other sites
4 minutes ago, MiniYoda said:

 

I'm going to avoid the Obsolete recipes, since my column for HME will reference current products.  Perhaps someday Mr. Beer can find a way to bring obsolete recipes back to life with current HMEs.  I am adding the Archived ones, though, even if they are listed as "sold out".

 

I thought about the rating system, but that would be a later phase if/when the database comes online. There's no way to do a live rating system on a stagnant spreadsheet.  Once we get it live, we can do a rating, but I need to make sure one person can't do the rating more than once.

 

Quick question.  I have a field for ABV, but should I include fields for OG and FG?  Now is the time to add fields, before too many records are entered and I have to go back to find info

I would say yes - for the OG, FG.

You will need them probably if you plan to accommodate Mr Beer's future plan to supply All Grain recipes anyway. So think too,  about what you need for All Grain. 

And some user recipes will be all grain probably.

Even though I do not monitor these values, I think a lot of Mr B brewers do, and Mr B does give the values in the recipes. I would include ALL the parameters given by Mr B, as a minimum.

Share this post


Link to post
Share on other sites
16 minutes ago, Nickfixit said:

@MiniYodaSorry to hear about the SWFFD reason for free time. I went through that many years ago.  Having another thing to focus on is good, if there is no chance of reconciliation, otherwise I would say drop the brewing and focus on that.

 

Thanks, Nickfixit.  Despite best efforts, she didn't want to reconcile.  It's for the better for me, and if she thinks better for her, more power to her.  And look at it this way, if it weren't for the divorce, I would never had brewed my first beer.

Share this post


Link to post
Share on other sites
Just now, MiniYoda said:

 

Thanks, Nickfixit.  Despite best efforts, she didn't want to reconcile.  It's for the better for me, and if she thinks better for her, more power to her.  And look at it this way, if it weren't for the divorce, I would never had brewed my first beer.

Always good to find "silver linings"

Share this post


Link to post
Share on other sites

I doubt if any woman will have anything to do with me unless she's a gung ho beer drinker! my house is basically a storage and small brewing facility, I guess I have to thank my depressive state of mind I was in after I left the local liquor store after purchasing a case of craft beer and realizing it was almost $80.00!!!, then I made up my mind I can do better than this!! on and off I buy craft beer and its just getting way too ridiculous to buy anymore, one of the reasons i'm going 10 + gallons

35 minutes ago, MiniYoda said:

 

Thanks, Nickfixit.  Despite best efforts, she didn't want to reconcile.  It's for the better for me, and if she thinks better for her, more power to her.  And look at it this way, if it weren't for the divorce, I would never had brewed my first beer.

 

Share this post


Link to post
Share on other sites

Version 0.2 of the spreadsheet attached.

 

Added several fields (thanks for all the suggestions) , and entered all the archived recipes plus one regular (Pumpkin Spice).  Total complete is 16.  There are some recipies that have a red block somewhere in their record, such as "season", "Ideal Temp" or "conditioning", because I couldn't find the information on the web page. If someone can send me this info, I'll added it.  Also, I'm open to suggestions on Season.  Should it be the season of the year?  or should it combine occasion, such as "Fall/Halloween"?  *OR*, should occassion be a separate field, so that Read White and blue would be Season=Summer, Occasion=4th Of July?

 

So far there are two recipies that have been "yellow lined".  These are a Limited Edition HMEs, and not a recipe which will be around for a long time.  I think they, and other Limited Edition HME's, should not be included.

 

Added the ability to filter out just the items you want.  Try this.  Click on the down error next to HME (column K), click the checkmark next to "(Select All) to turn off all the check marks, then check only "1 can American Ale".  Three records will show up.  Now click on the arrow for Flavor (Column D), uncheck ALL, and check "Hoppy".  You've now found all the recipies (so far) that you can use your extra can of American Ale that have a hoppy flavor.  Yes, I know there one recipe that calls for an American Ale and an American Lager, and it goes away when you just select Ale, but that is very easily resolved when this goes to database/web page.

 

The numbers for Fermenting, Carbonating, Conditioning, and Total Time represent number of weeks.  So far, all have the same 3 weeks of fermentation, and only Pumpkin Spice has a different number for carbination.  I'm still wondering if we need the column for "fermentation", since that never(?) changes from 3, and just make Carbination and Conditioning one column.  Once this goes into a web page, the number of weeks can be converted to months (or both weeks/months can be displayed)

 

Some of the text fields are complicated to read, but the database will handle this.  For example, LEO IPA has three different malts, and three different hops.  Not very clear to read as a single cell in a spreadsheet, but using databasing the info can be stored nicely and using web pages it can be displayed more clearly.

 

As for the full-grain suggestion, I'm not opposed, I just don't have a recipe I can use to see how it will fit in the spreadsheet.  If someone one wants to message me with one, I'll make it work.

 

The field Special Instruction (column AB) is the one that gets me.  It's either "none", or some long paragraph.  In one case, LEO, I said "to hack with this" and put in See Directions.  I'm thinking for Mr. Beer's recipes, just "follow directions on web link" and people can go to the link in the next column to read their step-by-step directions.  Easy to do on a web page.  For people adding their recipies, this is where you give step-by-step directions.

 

Thanks for everyone's input.  I will try to add more recipies this evening, and any suggestions you might have for changes.

MrBeerRecipes.xlsx

Share this post


Link to post
Share on other sites
2 minutes ago, MRB Tim said:

 

 

You might have to switch from the Product Information tab to the Instructions tab on the product page to see some of those, others are listed on the info tab. We could probably do well to standardize that a bit


I tried to do this, and caught it on most of the recipes, but might have missed some.  If anyone wants to pass along any mistakes, or can provide better info, I'd be happy to update the spreadsheet.  Eventually, when this goes live on a web page, we'll set things up so that anyone at Mr. Beer and perhaps a few select others can be admins for the database and can fix issues.

Share this post


Link to post
Share on other sites

Next..... to write software that will read the Mr B web pages and update the data in the database/spreadsheet.

Getting the spreadsheet to complete from a database is ok but I have not seen doing it from web site. A standardised web page format would surely help.

You probably have this nailed but for others this may give some ideas.

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708F2249-9569-4FF9-A8A4-7EE5F1B1CFBA

 

Share this post


Link to post
Share on other sites

version 0.3 of the spreadsheet.  Tired of this yet?

Total recopies so far - 27
Removed the seasonal HME-only recipes. 
Added a column for mash.  For now, it's either "no" or "partial".  Suggestions are welcomed
Removed "can" from the HME column


Is it safe to assume that if three recipes have "Temp Range" at 59-75 and Ideal Temp at 67, then any recipe with the same temp range should have that same Ideal Temp? 

Let me know if you have any questions/comments

MrBeerRecipes.xlsx

Share this post


Link to post
Share on other sites

And you thought you could just ignore me.  HA.  Here's version 0.5 of the spreadsheet (0.4 was an internal update)

 

- removed the Seasonal field.  This is somewhat subjective for now.  If/when it becomes a true database, I'll add it, but there is no reason to have it for now

- added page 2 of Mr. B's recipes, and part of page 3.  Sorry, I'm tired, a bit tipsy, and have to get up sometime in the morning.  I'll add more tomorrow

- Fixed a few spelling mistakes, and changed the wording of some data for clarity

 

I found several mistakes as I add more recipes.  Please.......feel free to look it over and send me corrections.  For now, column AB (Special Instructions)....We can deal with that later.  It's a place holder

 

Please feel free to Personal Message me any change so to not clutter thread.  Next update will be Wednesday (late) evening

 

 

MrBeerRecipes.xlsx

Share this post


Link to post
Share on other sites

yea........I'm Back

 

version 0.6.  Getting closer


- Finished page 5 of Mr. Beer's recipes
- Merged "Malt" and "Grains" to just Malt.  Not sure what I was thinking, but this is obviously the same thing.
- There were some recipes that require malts, but weren't shown as "Partial Mash".  Please correct me if I am wrong, but I believe that if you have to steep grains, this makes it a Partial Mash.
- Fixed a few things here and there.  Spelling mistakes, etc.

- Fixed issues with HME, LME, DME and Yeast.  This should make searching easier.  Will work on Hops on the next version.
- Unless there is a reason not to do so, on the next version I will remove "Fermentation" and "Conditioning".  I'm not aware of any situation where these should not be 3 weeks each.  Please let me know if there is.

 

 

Haven't heard from anyone yet about the spreadsheet.  If you have a free 15-30 seconds, please feel free to look things over and give me your opinion.  Post to this thread or personal message me. 

 

 

Thanks in advance

MrBeerRecipes.xlsx

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×