Keeping Your Preps Organized
There are two words I’ll bet you never thought to see together. After all, you can’t eat or drink a database, it won’t keep you warm or cool, and it won’t drive off predators, either four legged or two.
But, you probably have some food and water, some fuel and ammunition. Do you have enough? And where is each batch? When does the next item expire? What is the serial number and cost of some item which got lost or destroyed (for an insurance claim)? Keeping track of it all CAN be done with paper and pencil, but a database can do it better. As long as the power stays on, that is. Fortunately, any decent database can print out your inventory after significant changes.
Basic Database Theory
What is a “database”? At the most simple level, it is a collection of data. For each entry into the database (for an item), there is a “record”. Each record consists of a number of “fields” (pieces of information about that item). The collection of records and fields is called a “table” For instance, let’s consider the following very simple database:
|Type||Contents||Servings||Date Bought||Expiration Date||Cost||Count||Location|
|Freeze Dried||Spaghetti||4||3/11/2009||3/11/2019||7.89||5||Under Bed|
The rows are the “records” and the columns are the “fields”. So far, nothing which can’t be done with sheets of paper and a pencil, except adding, deleting and modifying paper can get messy quickly. Say you wanted to add a new field, “Calories per serving”? Fairly trivial for a decent database program. How about buying new stuff, with differing expiration dates? Or splitting up things into multiple locations? Again, the database should be easy to change.
But still, we are just “improving” the paper and pencil concept. The real power of the database comes from the ability to search and compute from the data. For instance, from the above database, you could ask for the number of servings you have or the cost per serving or the total amount you have invested. Or you could see what will expire in the next 6 months. And so on.
Most “serious” database programs support a powerful Query language called SQL (for Structured Query Language). For instance, to find out what expires in the first quarter of 2016 from your database table above, the command would be something like
So far, we have discussed common “flat” databases. The key aspect of this type is, every single record contains every single field. With a low number of items, this is not a problem, but if you have a lot of items with a lot of fields, it gets tedious to enter them all, and it takes up more space than necessary. The solution to this is a “relational” database. Here, you have a number of tables which are “related” to each other. That is, each table contains “some” of the fields, and other tables have fields which “point” to fields in the other tables rather than containing the fields themselves. For instance, here is a database I’m generating to keep track of the contents of my kitchen (to aid in shopping and eventually selecting recipes based on what is on hand):
Here we have tables Ingredient, Location and Item, where Ingredient is a generic type of ingredient, Location describes a place where ingredients are stored, and Item is a single (specific) instance of an Ingredient. In Item, the blue fields are not stored in the Item database, but only exist as “pointers” to the Ingredient and Location databases respectively.
There is another table defined but not displayed, Nutrition, which gives calories, fats and such. It has (will have) too much data in it to be included in the view of Item; so it is displayed by clicking on the Nutrition button, and scrolling to, in this case, record 6.
This set of nutrition data came from the package itself. Which package? There is a pointer (the blue field) back to Item record 19.
Databases are very useful, and so, very popular; there are a lot of them out there. Do a search online for “relational databases” (you may not think you need the relational option, but better to have it and not use it than not have it and have to move to a new database system if you ever do need it) I used IBM DB2 on mainframes for years, and it appears they now have Linux, UNIX and Windows versions, but with the exception of a basic “Express-C” version, they charge for them. Oracle is a well known database as well, and I’ll bet pricy too. Microsoft has one; I try to avoid them whenever practical. I’ve heard of MySQL, but I found a page of their versions which listed prices from $2000 to $10,000! Perhaps a better search would be “free relational database”. Doing so implies MySQL does have a free version in addition to those high priced versions.
I found an interesting possibility which has a graphical user interface as well as supporting a powerful programming language. It is called MUIbase.
From the manual:
“MUIbase is a fast and flexible database system with graphical user interface (GUI) and programmability. It is targeted for advanced desktop users who want to manage data in a comfortable and powerful way. MUIbase is able to manage any kind of data, e.g. addresses, CD series, movies, photo collections, your family tree, your income and expense, and much more. The power of MUIbase lies in its clear and powerful graphical user interface and its programming capabilities. Programming MUIbase allows you to process data in various ways, e.g. automatic calculations upon user input, generation of reports, import and export of data, etc. For example it can be used for calculating the total amount of income, or the total amount of recorded time on a CD, or to automatically create and print serial letters to your customers.”
The screen shots above show the graphical user interface, a more “user friendly” way of entering and viewing records than is typical of databases. Note the “buttons” such as “GetOnHand” which can invoke programs to do advanced functions. This button, for instance, searches through all Item records for those which point to the Ingredient record from which the button was invoked, sum up the amount of that ingredient on hand, and computes the average cost of the ingredient. Here is part of the program for this button:
This may look a bit weird to you; that is because the programming language uses a “LISP”-like syntax. LISP is the second oldest high level programming language (FORTRAN is older by one year). It was particularly popular for artificial intelligence development; very powerful with superior recursion capabilities (ability to call itself). If I am remembering my class correctly, you can solve the puzzle where you move a stack of sequentially smaller disks from one pile to another without ever putting a bigger disk on top of a smaller disk, in just one highly recursive statement. LISP statements generally have the format:
(function expression expression)
(+ total 5) add 5 to total
(IF (> total 15) (“then”expression) [(optional”else”expression)] ) IF/THEN/ELSE equivalent
Powerful, yes, but a bit of a pain to program. The many “(” and “)” are critical, and lose one or get an extra in there or put one in the wrong place, and if you are lucky, all that will happen is that you get an error during the compile attempt (it is a compiled language rather than interpreted, but the compiler is built into the editor so it is not as painful as some compilers make it).
It is available for Windows, Mac, Linux and Amiga.
Experiences with the program
Initially, there was a bit of a problem – my antivirus program kept “quarantining” the program (so it appeared to download successfully but then disappear). Not because the download had anything wrong with it; just the antivirus decided that not enough people had reported experience with it. I told the antivirus to release it from quarantine, and had no more problems installing and starting the program.
There is substantial documentation, which is pretty well organized and readable, but has some holes. It mentions some concepts, but finding the details is sometimes a challenge, as the index is a bit incomplete. Trial and error are sometimes required to figure things out which are not explained with enough detail. There are a number of sample databases to provide examples.
So far, I’ve been able to get it to do everything I’ve wanted it to. This really is a fully functional database solution. I have a list of “desired updates”, which shrinks as I find ways to do the things faster than I think of new things I would like to do.
You swap back and forth between “structure edit” mode and “data edit” mode (the views of the database shown above). This is done from a drop down menu, so is not quite as convenient as it could be, but is easy to get used to. There is a Cntl key shortcut to swap back and forth, which should be more convenient for those who are used to that methodology. Structure edit mode can be used to control the display of each table and the entire database. Program editing is done in a separate, independent window.
Support is pretty spectacular. My questions were quickly answered, and a minor bug was fixed immediately.
The program is free. It is possible to support it (make a donation) if you find it as valuable as I do.
The program and documentation are available here.
Do you have a survival database or spreadsheet? Share your knowledge in the comments below!