As I've been experimenting more with MySQL's stored procedures, I have found that there are very few examples online. Questions I had about the stored procedures were difficult to answer. So, here's an archive of stored procedure examples that I have written for a current project. Hopefully they will help someone.
This article the 3rd installment in of a series titled 'How I build an application'. You can find the other two installments and subsection here.
I mentioned last time that I would discuss why I choose certain data types for certain fields.
Recipe Ingredient Table
--
-- Table structure for table `recipe_ingredient`
--
CREATE TABLE IF NOT EXISTS `recipe_ingredient` (
`recipe_ingredient_id` int(10) unsigned NOT NULL auto_increment,
`recipe_id` int(10) unsigned NOT NULL default '0',
`unit_id` int(10) unsigned NOT NULL default '0',
`ingredient_id` int(10) unsigned NOT NULL default '0',
`unit_amount` tinyint(3) NOT NULL default '0',
PRIMARY KEY (`recipe_ingredient_id`),
KEY `IDX_recipe_ingredient_recipe_id` (`recipe_id`),
KEY `IDX_recipe_ingredient_unit_id` (`unit_id`),
KEY `IDX_recipe_ingredient_ingredient_id` (`ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;This table is one that ties a lot together, so we'll start here. This table contains a primary key and three foreign keys. I try to always construct my integer keys using a display length of 10, marking them unsigned with a default of zero. Whether a field is marked at 'NULL' or 'NOT NULL' depends on the design. read more »
Onto the next stage. I've got my vision nailed, now it's time to start building.
This article is a continuation of How I build an application - Part 1. Check it out if you missed it. Last time, I came up with the idea of a little tool to help plan meals with the goal of eating healthier and spending grocery money more efficiently. I came up with the following drawing for my database.
