If you look at my original post, you'll notice a bunch of dates have been changed. When I recreated my timeline, I thought June was the last month this semester. I received my biweekly phone call from my mentor and guess what? My semester ends in May. Furthermore, I need to submit everything for grading early in the month so I have time to fix anything that needs fixing. All I can say is... yikes!
With so little time left, I really have to try to speed up this process a bit. But I also need to maintain this blog to give me reference points for my project documentation later. So here goes - a recap of what I have encountered thus far in my project.
In my original timeline, I allotted one month to design the SQL tables. I thought, 'hey, I administer a database every day and know a lot of this SQL stuff pretty darned well.' Apparently, I didn't know it as well as I thought.
To begin with, though I had a general idea of how the different tables would work together, I didn't realize how hard it was going to be to deal with multiplicity. One-to-one relationships were very easy to code but one-to-many relationships proved much more difficult. I finally realized that the best way (perhaps the only way?) to set up these relationships was to create a separate table for anything that would be used more than once and have it reference the original piece. To clarify this, let me use an example from my project:
A recipe is composed of a number of things. I've opted to store a lot of metadata in the form of parameters for the purposes of sorting, storing, and retrieval. I've then divided the recipe into two pieces of indeterminate length, instructions and ingredients. Since I don't know how many instructions or ingredients will be in a recipe, I was stuck with figuring out a way to handle the one-to-many relationship. In my mind, I was thinking that a recipe contains a bunch of these things so somehow it had to be able to reference any number of them. It turned out that I was thinking about it the wrong way and I really needed a number of, say ingredients, that would all reference the one recipe.
Once I understood how to handle these relationships, I moved on to another topic - data validation. I wanted a number of elements to be selected from controlled but extensible lists. A good example of this is the ethnicity of a given recipe. I figure users are going to want to have the option of looking up recipes by their ethnicity - maybe they feel like Mexican tonight or something. While I may be able to think of a lot of ethnicities (Chinese, American, Italian, etc), I don't want to try to create a giant list with every possible ethnicity. And a list of that size would be unmanageable. So instead I wanted to create a few to get the user started but then allow the user to extend that list.
Ethnicity isn't the only category I want to be extensible. I want users to be able to pick out recipes based on ingredients. Since I've divided ingredients into counts, measures, and other factors, I needed a way to create an ongoing, non-duplicated record of foods in recipes.
To be very valuable with regard to sorting and searching, these lists have to be controlled. After much internal debate, I decided to create individual tables for each of these things. The tables only have a single value, the primary key representing the ethnicity, foodstuff, etc. And I'll use these tables as foreign keys for their parent tables so the limitation is enforced at the lowest possible level, the database.
There were a number of validation lists that won't be able to support expansion as their contents will map to GUI fields and other contexts. To be consistent with my validations, I created these the same way as the extensible lists - tables acting as foreign keys to their parent tables.
I created all my tables in an Entity Relation Model (ERM) using MySQL workbench (an amazing free table design program!) which enabled me to forward engineer and create the database directly from the ERM.
Perhaps I should back up a bit. Unfortunately, this is my first time ever using MySQL - I use MS SQL 2000 at work - so I don't really know that much about how to properly configure MySQL. It ended up taking days of work, perhaps even weeks, just to get the database to 1- run, 2- let me connect to it, and 3- let me make changes to its structure. That was valuable time right down the toilet. I finally figured out how to run the server as a localhost and how to get the rest of MySQL workbench to talk to it, but to be honest, I'm not sure I could recreate that success without some serious further study.
Having sorted out all the server configurations, I tried to use the forward engineering capability of the workbench. Unfortunately, a bunch of my foreign keys refused to work! It turns out that in MySQL, when using the INNODB engine, each foreign key relationship has to be given a name. I'm not sure what the name is for, since I don't think it shows up in any dialect of SQL, but apparently these names also have to be unique. Foolish me, I named all of the foreign key relationships after their foreign key fields. I lost another chunk of time on this because the error MySQL gives doesn't tell you this at all. It gives an error number that could refer to any number of things. So figure in a few more annoying days trying to find out what the error meant and how to resolve it.
Once I got the tables about how I wanted them, I set forth to create views to easily and automatically pull out shopping lists. Since my database is increasingly normalized, this turned into a monumental task as well. I'm actually not certain they work yet because it's too time-consuming to create records for a few recipes. At least, until the program is finished...
That commentary covers January and February, two months that turned out to be WAY to short for what I needed to accomplish. On the plus side, the data store is 1/4 to 1/3 of my total project (assuming each component is equal in time and difficulty) so I already have a deliverable to show for my efforts.
