Defining Databases for the Yii Application

This is the fourth post in my series on Yii, my favorite PHP framework. [intlink id=”473″ type=”post”]In the first[/intlink], I show how to download and test the framework itself. [intlink id=”563″ type=”post”]In the second[/intlink], I show how to create a basic Web application. [intlink id=”583″ type=”post”]In the third[/intlink], I walk through some configuration options. In this post, I want to discuss the database design that will be the foundation for the Yii application I’m demonstrating (in the subsequent four posts). The specific example will be a classic employees-departments application, with each employee in one department. This post does assume you have an existing Yii application to work with, most likely by following the steps in my previous posts.

(Note: In October 2010, I’ve updated this entire series to reflect changes in Yii since this series was written, and to take into account feedback provided through the comments. Some outdated material will be crossed out, but left in to reflect how things have changed since the series was begun in June 2009.)

The goal here is to create a Web application that allows you to add, edit, display, and delete the departments and employees of a hypothetical corporation. These four features are known as CRUD: Create (i.e., INSERT), Retrieve (i.e., SELECT), Update, and Delete. These four acts are the cornerstone of every content management system, and just about any other data store as well. To pull it off, you’ll need to create a series of files for both employees and departments:

• A Model file, which defines the thing itself
• A Controller file, which defines the actions that can be taken with the thing
• Several View files, which act as the user interface, including forms for adding new items and updating existing ones

With some frameworks, doing all of this requires quite a bit of busywork: selecting a template, copying, pasting, editing, creating new files, etc., etc. With Yii, and with other frameworks like Ruby on Rails, the majority of the work will be done for you by doing the proper preparation and then invoking the built-in tools. To start, then, I need to think about the Models, which will be represented by database tables in this example.

The two tables will be rather simple, with a one-to-many relationship between them: there will be many employees in each department but each employee will only be in one department. You can put whatever you want in your employees table, but for the purposes of this “Learning the Yii Framework” series, it’ll be defined with this SQL command

CREATE TABLE Employee (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    departmentId TINYINT UNSIGNED NOT NULL
        COMMENT "CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)",
    firstName VARCHAR(20) NOT NULL,
    lastName VARCHAR(40) NOT NULL,
    email VARCHAR(60) NOT NULL,
    ext SMALLINT UNSIGNED NULL,
    hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    leaveDate DATETIME NULL,
    INDEX name (lastName, firstName),
    INDEX (departmentId)
)

[intlink id=”505″ type=”post”]In a post on MVC[/intlink], I discuss conventions and how it’s best to just go along with the framework’s rules. For example, I normally use all lowercase and underscores for my column names (e.g., first_name) but I am using camelcase here to be in keeping with Yii and the general OOP standards (when the values are retrieved for an individual employee, specific columns will be referenced using, for example, $model->firstName). The same goes for the primary key column, which should just have the name id. The departmentId column will be a foreign key to the departments table, to reflect which department the employee is in. I’ll address the added comment shortly. The ext column is intended to represent the employee’s phone extension (mostly I put it in there to demonstrate numeric constraints in the Model). The hireDate is populated when a row is inserted (if no date is provided for it). Do note that I’m assuming MySQL here, and how timestamps behave does change from version to version. And there’s an index on the person’s name for sorting purposes, plus one on the departmentId column as it’ll be used in joins.

The departments table only contains three two columns. The first is the primary key; second is the name of the department (a unique index is placed on it), and the second is the department head. That value will be the ID value from the employees table for the associated head. An index is placed on the deptHeadId column, as it’ll be used in joins. (And, this means there’s also a one-to-one relationship between the two tables.) (Note: I dropped a third column from the original version of this series as it was confusing to many people.)

CREATE TABLE Department (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(40),
    UNIQUE (name)
)

Now, as for the comments in the Employee table…Yii will recognize FOREIGN KEY constraints that exist and use that information to help define the Model. But since MySQL doesn’t always enforce foreign key constraints (depending upon the version of MySQL and the storage engine being used), you can fake the constraint for Yii’s sake by adding a comment like the one associated with the Employee.departmentId column. That comment suggests that the the departmentId column is a foreign key that references the id column in the Department Model. If you don’t add this comment, it’s not a big deal as you can write the code to indicate the relation yourself, but it’s nice that Yii will do it for you.

Okay, that’s it! Once you’ve created the two tables, you can return to the Yii tools to create the Models, Controllers, and Views (as I’ll do in the next post). Before you do so, you should revisit your database tables to make sure they are exactly how you want them to be. Since Yii will auto-generate a bunch of code for you, any changes you make after Yii writes this code means you’ll need to go in and manually edit that much more code. So get it right now, as much as you can!