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.

I set my INT field display lengths to 10 out of habit more then anything. MySQL doesn't save space by specifying a smaller display length, it always uses 4 bytes to store an INT field. For more on the details of display widths and the limits of what a field can store, check out MySQL's documentation on numeric types.

I also mark all my integer keys as unsigned. Because I usually auto-increment my key fields, I really don't have a need for negative numbers here. By marking the number as unsigned, I double the size of numbers I can store here as well, without jumping up to a larger field type like BIGINT.

Lastly, I set a default of zero. Because of foreign key constraints and business logic in the system, this shouldn't be needed. However, it's a good habit to set defaults at every level of an application. Setting a default of zero is important if you plan to create a 'zero to many' relation, but those are fairly rare as well.

The last field in this table is the quantity of the ingredient for this recipe. For example, if I need 2 eggs for chocolate cake, the number 2 goes in this field. Because this will typically be a small number, I use MySQL's TINYINT field type. This is a 2-byte numeric value.

I put my key definitions in with my table definition. This could easily be broken out, but I like seeing my fields and keys in one place. I also follow a naming convention when naming my indexes. I prefix every index with 'IDX_', followed by the table name and then the field name.

Lastly, I am using the InnoDB table type because I am making use of foreign keys. I also set the CHARSET of the table to utf8. I try to do everything using utf8 now as I think it is just a good habit to get into. You could fill a book with a discussion on character sets, but suffice it to say that utf8 is a good place to be.

Menu Table

--
-- Table structure for table `menu`
--

CREATE TABLE IF NOT EXISTS `menu` (
  `menu_id` int(10) unsigned NOT NULL auto_increment,
  `meal_id` int(10) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`menu_id`),
  KEY `IDX_menu_meal_id` (`meal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This tables makes use of the same field types I use for my primary keys and foreign keys. The purpose of this table is to tie together meals and menu's in a many-many relationship.

The only new addition here is the date field. Dates are an interesting beast when it comes to data storage. I ran into a huge problem on another project where I was using a timestamp field when I really needed just a date field. My advice when looking at a temporal field it to research your business need very carefully. In this case, we only need the granularity of 1 day, so I use the DATE field tyupe.

Ingredient

--
-- Table structure for table `ingredient`
--

CREATE TABLE IF NOT EXISTS `ingredient` (
  `ingredient_id` int(10) unsigned NOT NULL auto_increment,
  `ingredient` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The new field in this table is the VARCHAR field type for the "ingredient" field. In my experience, it's usually best to use the VARCHAR field type when working with MySQL. You can read up on the gritty details on MySQL's documentation page. The short version is that MySQL stores the full length of CHAR fields regardless of their contents, padding the value with spaces. MySQL does not pad a VARCHAR field value with spaces, saving a small bit of space, but using an extra byte to store the length. There's a minor bit of overhead in calculating the size of the field.

The rule I use to decide between CHAR and VARCHAR is based on two things. First, a field size with a maximum length under 10 I will generally use a CHAR. Any field with a length greater then 10, I will use a VARCHAR. The only exception being a field with a defined length that I know won't change. For instance, I'd use a CHAR for an alpha-numeric code that will always be 12 characters.

Recipe Table

--
-- Table structure for table `recipe`
--

CREATE TABLE IF NOT EXISTS `recipe` (
  `recipe_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `instructions` text NOT NULL,
  PRIMARY KEY  (`recipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The new field type in the recipe table is the TEXT field. TEXT fields are a BLOB type field that store their contents as character strings instead of binary strings. The "instructions" field will contain a paragraph (or two) of text describing how to build the recipe, so this field was appropriate. While the TEXT can hold a very large amount of text, MySQL only stores what is neccessary for this field type, just like the VARCHAR field type.

Meal Table

--
-- Table structure for table `meal`
--

CREATE TABLE IF NOT EXISTS `meal` (
  `meal_id` int(10) unsigned NOT NULL auto_increment,
  `meal` char(9) NOT NULL default '',
  PRIMARY KEY  (`meal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is the first of what I call "Type" tables. A type table is simply an easy way to keep a set of values that will remain constant in your application. In this particular case, this field will contain three rows, 'Breakfast', 'Lunch', 'Dinner'. Knowing that my plan is to only contain these three values and the length will be less than 10, I make the choice to use a CHAR field type instead of a VARCHAR. I've discussed the differences of these two field types above.

Unit Table

--
-- Table structure for table `unit`
--

CREATE TABLE IF NOT EXISTS `unit` (
  `unit_id` int(10) unsigned NOT NULL auto_increment,
  `unit` char(10) NOT NULL default '',
  PRIMARY KEY  (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My Unit table is another example of a "Type" table. One difference between the Unit table and the Meal table is that I don't know all of the values that will go into the Unit table. In my 'initial_data.sql' file, I've added a few basic cooking units such as Cups, Tsp, Tbsp, Oz and Lb. However, this will grow.

I know I'll need to create a screen to manage this table, but I don't want the field size to grow too large. That's the reason for using a CHAR here instead of a larger VARCHAR field.

Menu Recipe Table

--
-- Table structure for table `menu_recipe`
--

CREATE TABLE IF NOT EXISTS `menu_recipe` (
  `menu_id` int(10) unsigned NOT NULL default '0',
  `recipe_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`menu_id`,`recipe_id`),
  KEY `recipe_id` (`recipe_id`),
  KEY `menu_id` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've already discussed the field types in the above table, but I want to include it here for reference.

Conclusion
Why spend so much time with field types? Your database is an application's foundation. Many other design decisions are based upon the database design so it is critical that every detail is correct. I've learned a lot from the book, Pro MySQL by Kruckenberg and Pipes. The MySQL documentation is also a great resource for additional information on the differences between field types.

Next time we'll get into the initial UI design.


»