Start of Pantry Application (db design with MySQL Workbench)
Posted on January 1st, 2011
One common problem I have is determining what I have in my pantry. For example, I’ll go grocery shopping after work and not remember whether I have any apple cider vinegar and if so, if I have enough for the recipe I want to make. Often I’ll buy a bottle and then get home to discover that I already have plenty. Or, I’ll assume I have something I don’t and get home to find out that I am now missing a key ingredient. Clearly, I need a software solution to my problem and just as clearly, I want to create it myself regardless of it already existing.
There are several decisions I need to make, platform being probably the hardest. For now, I decided to start out by designing the data model so I don’t have to worry about the hard stuff. Obviously, I need to keep track of the food items, categorize them in some way and track the amount on hand.
Then I decided that I wanted my ERD to be pretty for this blog post, and I decided to try doing this with MySQL Workbench (http://www.mysql.com/products/workbench/). Good news is it comes for linux! Bad news is that I was kind of a flake and tried installing the wrong version the first time, but 10 minutes later when I figured that out, installation was very easy (Windows easy, even!).
To test how easy the software is to use, I decided not to read the manual before starting. I opened it up, clicked on File -> New Model. From there I built some tables. One thing I realized right away was that my computer is not strong enough at all to make this a pleasure to do. Also, for some reason it goes way off my screen (and doesn’t maximize correctly).
A few hours later (hours because my computer does not handle this software very well *) I broke down and had to read the manual. Starting my diagram using the tool was pretty easy, you drag and drop the tables. However, the EED tool didn’t work the way I thought it would :) Oh well! Had the tool that I needed in order to create fk relationships using existing columns not been off the screen then I might not have had to resort to checking out the manual. Also, it shows the table relationships, but the lines don’t match up with the actual columns which can be confusing if you are just looking at it. Within the tool, you can hover over the relationship and it will show what fields are being used. Another thing to note is that since it’s not easy to alter the look of the relationships (and define them further), I couldn’t define the cardinality/modality in the manner that I am used to with crow’s foot notation (http://www2.cs.uregina.ca/~bernatja/crowsfoot.html). Of course, maybe it is possible and my lack of excitement about spending any more time on this just got in the way :) Anyone who knows more and would like to share their expertise in my comments, please feel welcome.
So, my first go at using the tool to define my data structure and making it a picture is here:
I think it’s all pretty self-explanatory. When food is purchased, the purchase is put into the purchases table and the amount in the food table increases. I’m not keeping track of each use, that’ll just be taken care of programmatically.
My next step is to determine the technology that I’ll use. I’m leaning towards django but am open to suggestions. At some point I’ll want to make a phone application (android) for it as well, since that’s where its best use will be.
That’s it for now!
Jen
* I’m sure that the software would be wonderful and fast to use on anything besides my netbook.