Your favorite database idea (1 Viewer)

[RossWindows]

I like how David Eager laid it out:

Realistically, something the size of McDonalds is not going to be using Access to do this, but the thought processes behind it is exactly the same: I have ingredients, I have recipes, I have a pantry - don't let the pantry get empty. What could be simpler?

 

[Steve R.]

An aspect of all databases is the ability to search for the "correct" data. Specifically, I am thinking of search engines, such as Google, but they still store and manipulate data. My thought? The ability of the database to understand what you are searching for (artificial intelligence (AI)).

My issue revolves around issues such as searching for product reviews or articles. Recently, I searched for a particular battery (specification) used in watches. Many, as in majority, of the hits related to selling the battery, not providing you with the battery specifications. In fact, some of the sites claiming to have the battery, did not even have it. So it would be nice to filter out all this commercial junk through an effective AI.

Now, I realize that companies purposely "game" the search engines so that you will unsuspectingly stumble into their website and that we really can't stop that type of abuse. For example many companies have "product reviews" that are nothing more that a sales pitch, so the AI would need to differentiate a real review from a fake review.

Irrespective of whether its a webpage or a simple database, it would be beneficial to have an AI that can "know" what you want. I think this concept would become increasingly important as we move deeper and deeper into the information age and virtually everything we do is recorded.

 

[RossWindows]

I've given that a lot of thought also, but I have concluded that it may be near impossible; Probably not for the reasons you're thinking of though. I figure that a mojority of searches are carried out using terms and phrases that the user has chosen based on what they think they need. Another way to phrase it is; Users often perform searches without knowing themselves what they're looking for. So the problem is not building AI as smart as a human brain... the problem is building AI SMARTER than a human brain.

An interesting venture nonetheless!

 

[alktrigger]

Would you like a barcode scanner with that, to automate your pantry/fridge-inventory update?

Can we also include a scale for products that aren't spent after one use? Like flour, weigh it when you buy it and then weigh it each time you put it back in the pantry.

How about if you are adding a recipe list, having it list recipes you can make with what you currently have in stock? This in itself might be a beautiful database for your liquor cabinet.

 

[Kryst51]

I like that idea as well!

 

[David Eagar]

Can we also include a scale for products that aren't spent after one use? Like flour, weigh it when you buy it and then weigh it each time you put it back in the pantry.

Would that not be double handling? - If the db knows what you are cooking tonight, it should know how much of each ingredient is required - therefore it should automatically reduce inventory without the need to reweigh anything.

Unless, of course, you don't follow the recipe

 

[Kryst51]

Well, the inventory might show 2 bags of flour, but not how much flour is left in those bags....Not sure, one of those things that would probably get thought more about when you were designing the db. A recipe might call for 2 cups of flour, but would it know how much that flour weighs and therefore be able to subtract that amount from a 5 lb bag? Something to think more about surely.

 

[Banana]

Kryst51, are you thinking of Allen Browne's Quantity on Hand?

 

[Kryst51]

Not specifically, as I have never seen that article , but it covers the general idea. I'm curious about storing units of inventory that only get partially used....I wonder what Allen Browne would say about that? I am huge, however, and completely agree with Mr. Browne in that I hate storing the outcome's of calculations, and would avoid it if at all possible. But I wonder if it would be helpful in this kind of database to help with usage calculations for budget planning and stuff like that, you know, looking at inventory history and such. I dont think I could picture it, unless I was using it.

 

[Banana]

That's funny. I thought he did discuss that as well, but I don't see in that article I linked. It may be in other article he wrote (and he wrote plenty!), or my memory may be playing tricks on me. But yeah, if we want to track stocks using FIFO stocking, then we need to store information about stock including the date it arrived, quantity associated with *that* stock, rather than a global quantity.

So the table would look like this:

StockType 	 StockLot 	 StockDate 	 Quantity 	 UOM
Flour 	 	 1 	 	 1/1/2009 	 100 	 	 lbs
Flour 		 2 		 1/2/2009 	 100 		 lbs
Eggs 		 1 		 1/1/2009 	 48 		unit
Eggs 		 2 		 1/2/2009 	 96 		unit
Baking Powder 	 1 		 1/1/2009 	 30 		 lbs

And when you make something that requires 150 pounds of flour, 64 eggs, and 10 lbs of baking powder (what would be?? A doughnut? ) you would place a record in another table(s) listing the ingredients you had to use, *then* to see what you have on the hand, use a query (maybe VBA?) to calculate the quantity on hand. In that view, you wouldn't see flour lot #1 at all because it's now depleted, so flour lot #2 should still be there, showing only 50 lbs left. Ditto for Eggs lot #1, with lot #2 showing only 80 eggs and Baking powder with 20 lbs left. All calculation would be done on the fly, though of course you may want to do a periodic stock takes.

 

[alktrigger]

Would that not be double handling? - If the db knows what you are cooking tonight, it should know how much of each ingredient is required - therefore it should automatically reduce inventory without the need to reweigh anything.

Unless, of course, you don't follow the recipe

You have a point, but what about something like sugar. sure some recipes call for it, and you can calculate the after quantity using the recipe, but how are you going to account to the .5-8 teaspoons that you use in your coffee/tea in the morning, how about if you have guests and you serve them coffee? It may be double handling, but unless you're using whole items, you can never write an algorithm that will be 100% accurate

And by keeping track of expected and actual weight difference, you can easily calculate the amount of waste/excess you use. Looking at that information you can create a cleaner, greener kitchen.

 

[RossWindows]

How about if you are adding a recipe list, having it list recipes you can make with what you currently have in stock? This in itself might be a beautiful database for your liquor cabinet.

Unfortunately, it's already been done.
See webtender.com

 

[RossWindows]

...you would place a record in another table(s) listing the ingredients you had to use...

Could this be accurately calculated simply by weighing the product before returning it to the cupboard? or would we need another table for calculating unit conversions? I.e. If you had 100 lbs of flour, lbs being the UOM, but you used 1c. Would you want to store the difference in weight in the second table, or would you want to store the amount you used (1c) and calculate remaining stock using the conversion table on the fly?

I guess our niggest enemy here is the fact that many products are sold in units of weight, but when it comes time to use the product we actually measure by volume. I.e. Flour is sold by the pound, but measured by cups...

 

[Kryst51]

I would rather some kind of conversion table, as I do not have a scale, nor do I have room in my tiny tiny kitchen to store one. But that would be kind of hard, you would have to have weight factors for each item that gets used that way.

 

[Banana]

RossWindows-

Well, if we assume that flour has same density, we can derive the volume from weight or vice versa as long we know the density and the density doesn't vary significantly between different flours (and even if they did, we can make them a separate record).

But in more practical terms, I would say consistency matters more. If it's weight in, then it should be weight out. Or volume in for volume out. So recipes would have to measure stuff consistently, so perhaps when we add a new recipe record, we would do the conversation necessary to get the appropriate UOM so it is used consistently afterward in the application's lifetime.

Sure, we can come up with a whizzbang way to make it easy for people to choose how to enter and extract data, but I think back to Albert Kallal's excellent treatise on punch card machines, which should highlight the importance of making the process streamlined as possible.

 

[Kryst51]

I was thinking more along the lines of flour, sugar, salt, pepper, every other spice imaginable. The weight in/weight out thing is kind of hard when you deal with recipes, some recipes call for 2 tablespoons of flour, while another may call for 5 cups, depending on the recipe. I don't have time right now to read the article, but will probably be able to read it tonight. I suppose the conversion could come in the recipes UOM. If it is typed in as 2 tbl it could be converted to lbs or something in the reporting using the density or something like that.

 

[RossWindows]

I suppose it would be best to weigh it in, weigh it out. Maybe as part of the product, a precise scale could be included as an option. Then the database could see a difference in weight even if you only use 1/4tsp of something.
Then the only time the conversion tables would be necessary is when you pull up a recipe you want to make.

After a little while, actual stock may be different than reported stock due to variations in density, therefore we can incorporate a margin of error so the database can supress recipes that call for an amount of ingredient that may be negligable.

Also, we could create standard 'Recipe Stock Levels'
If all ingredients of a recipe have plenty of stock, the rating could be shown as 'Good'
If one or two of the ingredients show a negligable stock on hand, the rating could be shown as 'Fair'
If one or more ingredients show a stock level that is definately too depleted for the recipe, the rating would be 'Poor'

 

[Banana]

Another way to work around the UOM issue is to just take one cup but before you do so, weight the flour (the whole bag or at least a prepackaged container for quicker retrieval) before the one cup scoop then again after the scoop then record the difference. That also conveniently fixes the problem from slight variances and such and thus keeps the inventory levels in database in sync with what we actually have on hand.

 

[Kryst51]

Ross, I like that idea about the rating, at least for items that are like flour. Things like chicken breasts, and eggs are easier to count, rarely will you use half an egg, or a quarter of a chicken breast in a recipe, And if you use only an egg white, you don't usually conserve the yolk....

 

[RossWindows]

In order to streamline the process. I think we would need a precompiled table of incredients and their UPC codes. Or perhaps find a way to automatically retreive the information over the web. Does anyone know a website with a database of UPC codes?

** Nevermind, I found one. upcdatabase.com
I wonder if that can tapped into dynamically?

** Nevermind again. They release monthly updates.
The update is a full download of the entire database. Viola!