Main menu

How I build an application - Part 2 - The Database

Submitted by mikehostetler on Tue, 03/20/2007 - 09:29

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.

So, how do I translate that into a physical database? I usually use phpMyAdmin. It's a great tool Better than the MySQL tools IMO for slapping together a database. However, at that point, the job still isn't done.

Maintainable Database Design

It's only been the last month that I finally have figured out a good way to maintain my data design. Non-SQL code files are easy, I use Subversion and that was the end of the story. Data design is much harder because Subversion doesn't work well with a running MySQL Database.

Breaking it down
To keep my data designs in source control now, I create the following directory structure.

|-- build_all.sql
|-- drop_all.sql
|-- initial_data.sql
|-- constraints/
|-- tables/
`-- updates/

I then create files underneath the constraints and tables folders that relate to my individual tables and their foreign keys. The directory then looks like this.

|-- build_all.sql
|-- drop_all.sql
|-- initial_data.sql
|-- constraints/
|   |-- menu.fk.sql
|   |-- menu_recipe.fk.sql
|   -- recipe_ingredient.fk.sql
|-- tables/
|   |-- ingredient.tbl.sql
|   |-- meal.tbl.sql
|   |-- menu.tbl.sql
|   |-- menu_recipe.tbl.sql
|   |-- recipe.tbl.sql
|   |-- recipe_ingredient.tbl.sql
|-- unit.tbl.sql
`-- updates/

Each individual file contains either a table definition or a foreign key definition. Here's a quick look at what is contained in menu.tbl.sql and menu.fk.sql:

menu.tbl.sql

--
-- 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;

menu.fk.sql

--
-- Constraints for table menu
--
ALTER TABLE menu
  ADD CONSTRAINT menu_ibfk_1 FOREIGN KEY (meal_id) REFERENCES meal (meal_id);

I'll explain structure and why I chose certain field types later on. For now, I want to just communicate that I separate out the basic table definitions and the foreign key relations.

The Build Script
The top three scripts: build_all.sql, initial_data.sql, and drop_all.sql are all MySQL Batch Files. Simply put, they are sets of SQL statements designed to be executed in order, just like a dump file. The difference is that they pull in other files using the SOURCE statement.

build_all.sql

-- Host: localhost    Database: recipe_box
-- ------------------------------------------------------
-- Server version       5.0.26

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

--
-- Current Database: recipe_box
--

CREATE DATABASE /!32312 IF NOT EXISTS/ recipe_box /!40100 DEFAULT CHARACTER SET utf8 /;

USE recipe_box;

--
-- Table structures & indexes
--

SOURCE tables/ingredient.tbl.sql
SOURCE tables/meal.tbl.sql
SOURCE tables/menu_recipe.tbl.sql
SOURCE tables/menu.tbl.sql
SOURCE tables/recipe_ingredient.tbl.sql
SOURCE tables/recipe.tbl.sql
SOURCE tables/unit.tbl.sql

--
-- Table constraints and relations
--
SOURCE constraints/menu.fk.sql
SOURCE constraints/menu_recipe.fk.sql
SOURCE constraints/recipe_ingredient.fk.sql

--
-- Initial Data
--
SOURCE initial_data.sql

/!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Broken down, here's what's happening:

  1. I create the database if it doesn't exist yet
  2. I then USE the database, to set where we want to execute after this point
  3. The table definition files are sourced, building my initial tables
  4. The files containing the foreign key information are sourced, creating my foreign keys
  5. I insert some sample data to make things a bit more meaningful

Isn't this the same as one big file?

Well, yes and no. It's true, the end result isn't different than keeping all of these statements in one large file. However, by breaking down each piece into a separate file, my source code management system becomes more useful. When I need to change the schema of the menu table, I simply change that file and I can re-create my database using this script. This script doesn't change, so when I look at a change set trying to find what happened, I know immediately it was a change in the menu table because the menu.tbl.sql file was the only file that changed. Slight difference, big impact.

Going the other way destroying the database

I also keep a file to get rid of the database. This is useful when I've changed something manually and want to move back to a fresh state. This file is included in the attachment if you want to check it out. It simply reverses everything the build_all.sql script does.

Initial Data

An empty database isn't that useful in testing. So, I've created a file to help pre-populate with some useful information. Here's the code:

-- Host: localhost    Database: recipe_box
-- ------------------------------------------------------
-- Server version       5.0.26

USE recipe_box;

--
-- Truncate and remove all data first
--
TRUNCATE ingredient;
TRUNCATE meal;
TRUNCATE menu_recipe;
TRUNCATE menu;
TRUNCATE recipe_ingredient;
TRUNCATE recipe;
TRUNCATE unit;

--
-- Dumping data for table meal
--

INSERT INTO meal (meal_id, meal) VALUES (1, 'Breakfast');
INSERT INTO meal (meal_id, meal) VALUES (2, 'Lunch');
INSERT INTO meal (meal_id, meal) VALUES (3, 'Dinner');

--
-- Dumping data for table unit
--

INSERT INTO unit (unit_id, unit) VALUES (1, 'cup(s)');
INSERT INTO unit (unit_id, unit) VALUES (2, 'tbs');
INSERT INTO unit (unit_id, unit) VALUES (3, 'tsp');
INSERT INTO unit (unit_id, unit) VALUES (4, 'oz');
INSERT INTO unit (unit_id, unit) VALUES (5, 'lb');
INSERT INTO unit (unit_id, unit) VALUES (6, 'slice(s)');

There are two important things to note here. First, I TRUNCATE everything first. It's nice to start with a clean slate, but I also need to reset my keys so the following lines won't produce an error. Second, by adding my USE statement at the top, this script stands on it's own. If my software changes the initial data, I can re-run this script to bring it back to it's original state.

Updates to live data

The 500 pound gorilla in the room is still there. How do I update a database with existing, production data? When yo look at updating a production database, there are several things to consider:

  • Updating your development copy
  • Testing the Update
  • Updating your live database
  • Ensuring your live data doesn't get screwed up

To update your main source, I simply update each individual table file or foreign key file and check them in. Because I'm using a source code management system, these changes are captured. This also keeps my build_all.sql script up to date, allowing me to re-create my database from scratch.

To ensure the necessary testing is done, I usually take snapshots of my data and store them in this file structure. I will only store data insert files, not files containing the table definitions. These are easily created through phpMyAdmin or mysqldump.

To update my live database, I'll typically write a script and store it under the updates folder. Capturing the update to a file has two benefits. First, I've got a change I can easily re-create if I need to try again. Second, a script helps prevent fat-finger mistakes when working with live data.

Ensuring the integrity of my live data is only accomplished through testing and guts. No matter how many times I test, or how many backups I have, I always get butterflies in my stomach when I run a update against a live database. This is probably good, but it doesn't make it any easier.

Conclusion

I wanted to get into a discussion about why I chose certain field types when building my tables, but I think that will have to wait for another post. It's been fun writing these descriptions of how and why I do what I do. Hopefully somebody somewhere will benefit from this.

AttachmentSize
recipe_box_db.tar.gz1.84 KB
Blog Topics: 
PHP
architecture
qcodo
mysql
database