This is the fourth post in my series on Yii, my favorite PHP framework. In the first, I show how to download and test the framework itself. In the second, I show how to create a basic Web application. In the third, 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)
)In a post on MVC, 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!
Another great post, Larry!
I am curious what resources (on the web, or amongst your sundry books and posts) will help me understand the truncated syntax of the sort of PHP that appears in these frameworks. I’m not sure what’s PHP and what’s part of the framework. I certainly like the terseness, but I want to keep straight what belongs to the frameworks so I don’t start trying to code weird statements into my strictly PHP projects.
Any ideas?
Thanks, Patrick. And you raise a very interesting point/question here. I haven’t actually written anything that clarifies this, although I can try to add a post in the future. In brief, the frameworks are using pure PHP code, the only thing is, it’s all OOP, so the PHP makes use of classes that are defined in the framework. So anything you see of the type $obj->method() or ClassName::method() is particular to that framework. I’m not sure if that’s helpful enough as is, so I’ll try to write this up sometime further down the line.
Thanks again! Larry
Hi Larry,
Thank you for this tutorial.
I tried your SQL for “CREATE TABLE Department” and I guess there is a missing comma after …REFERENCES Employee(id)’
You’re quite welcome. Thanks for the comments and for pointing out that missing comma. I’ve fixed it!
If I will write a general comment instead of the foreign key constraint then will yii able to get difference in them ?
Good question. I assume so, but haven’t tested this myself.
You say that you are using camelcase for the database names, but is not camelcase suppose to have the first letter capitalized? For example HireDate instead of hireDate?
As far as I know, camelcase allows for both hireDate and HireDate. The important distinction is that you use capital letters to separate words and not underscores.
Yes, according to Wikipedia AKA “People of the Internet”, Lower camelcase is hireDate and upper camelcase is HireDate, but both are considred camelcase. I never even heard of camelcase until I started reading your blog.
Thanks for following up with that. I guess if one wanted to be technical, a camel starts low, then goes high, then goes back low (and perhaps repeats again if it’s a two-hump camel). But I digress… I associate camelcase most closely with object-oriented programming, which generally recommends upper camelcase for class names–SomeClass, MyClass, or Class–but lower camelcase for objects and methods: myObj, someMethod().
I was trying to follow your Yii tutorial, and really thanks a lot, it was great this far, but I can’t get the MYSQL CREATE commands to run correctly… (error in SQL syntax) I think it’s because of the 2 foreign keys needing each other. If I create the Employee table without the COMMENT line it works fine and then I can create the department table just fine. But I don’t know how to add back that comment line, I tried with ALTER TABLE but I’m no good with MySQL. Either way I think creating the table like that is wrong, foreign keys should be added after with an ALTER TABLE query. Or maybe I’m doing something wrong?
MYSQL v5.1.36
Thanks for the nice words. Foreign keys are not often supported in MySQL, so I doubt that’s the problem. You don’t say where/how you’re creating these tables, but you can easily edit them to add comments if you use phpMyAdmin.
Hi, to create the foreign keys in MySQL you will need to make sure the data types are the same in both tables. Not sure if this is your problem, but it is a common one that prevents the construction of foreign keys…
Thanks, Peter. I think the restriction on both columns being of the same data type only impacts the efficiency of the foreign keys. I don’t think that a mismatch would prevent a foreign key from being established, although I could be wrong. Not using InnoDB tables will prevent foreign keys from being created, however. But in this case, the foreign keys aren’t actually being created, just comments indicating theoretical foreign keys that Yii should adhere to.
I’m using MySQL Query Browser, which is part of the official GUI Tools for MySQL. Copy/Paste the query and execute brings the error. I’m using WAMP also, so I’ll try to do that in phpMyAdmin then. I completed your tutorial anyway and added the lines needed in “relations” manually. Thanks for your fast answer!
You’re quite welcome!
First of all, id like to say thanks for this amazing tutorial…. thanks a lot…
Well im having the same error that Larry has got.
#1005 – Can’t create table ‘.\test\department.frm’ (errno: 150)
Image: http://i26.photobucket.com/albums/c110/nydiow/Riversoft/error.jpg
Im using MySQL 5.0.45
Thanks
Thank you for your fine set of posts on this subject. Things are quite clear so far.
I looked at the tables and it appears there is a circular fk constraint here where the first record in the Employee table requires a pre-existing record already existing in the Department table before they can be added, but the Department records require the employee be inserted first, thus the error.
I am wondering how mysql is resolving this without throwing fits, because I can’t imagine that you didn’t test this code, so the only conclusion I can come to is that it is MY conclusion that is incorrect, but yet I still don’t understand. Clarification please? Thank you!
Hello Garry. Thanks for the comments and for the nice words. The first thing to understand is that MySQL only supports FK constraints in recent versions and only when using the InnoDB table type. So, often, the MySQL server isn’t acknowledging FKs regardless. Second, the SQL command here doesn’t actually create FK constraints. What you see are comments describing the columns. These are for the benefit of Yii and has no impact on MySQL. To create a FK constraint in MySQL, you have to add a foreign key constraint clause to the table creation command.
That being said, if you were to actually create these tables using FK constraints, I could see there being errors when adding records, as you predict. In such a situation, I would probably NOT create the constraint between employees and the department head, as a department head could be a vacant position, as well as the logistic errors you suggest.
But, again, the comments in the SQL commands are really to help automatically build the relations between the two tables in the Yii Models. Hope that makes more sense!
Hi Larry, Its me again, i testing Yii following your application, by now is working 100%, i just have one problem with some words in my language (like não, cachaça, ímpar) those words have accents and when i use Yii and connect it with the data base, Yii didnt show those words.
Can you tell where i can configure it?
Thanks Larry!
Off the top of my head, I don’t know. I would check the Yii documentation for localization and internationalization. It may be the content-type set the main.php layout file. Or it may be the character set used to communicate with the database.
The database is ok, because im already using the database on other project and works fine, im quite sure thats about Yii configuration, but thanks, if you discovery something, id be happy =)
Cheers
I had an issue with relations() it remains empty,
though tables were OK, and I erased model files as well.
The solution was restarting yiic shell and command line,
then recreating model files.
Strange. Kudos for figuring it out and thanks for sharing!
with regard to Comments 11 and 15, I had the same problem. I found that the presence of the word “KEY” in the comment triggered the error. I could replace “KEY” with any other 3 letters and the table was created fine, so I just did that and then executed an ALTER to correct the comment.
ALTER TABLE `department` CHANGE `deptHeadId` `deptHeadId` MEDIUMINT( 8 ) UNSIGNED NOT NULL COMMENT ‘CONSTRAINT FOREIGN KEY (deptHeadId) REFERENCES Employee(id)’
Funny how it won’t take it in Create, but will in ALTER!??
this is MySql 5.1.41
Thanks for the energy you put into this, it looks to be a great framework.
Craig
Thanks for the nice words and for sharing your experience. It’s absolutely beyond me that MySQL would choke on a term used in a comment. It’s a comment! But thanks for posting your workaround.
Thanks for your work. I am finding this extremely useful.
I want to refer you back to comment number 16. I think Garry is touching on a small problem with the database design. Because there aren’t any fk constraints in the database, we can enter anything in the fk fields and not haver Mysql throw a fit. However, the “Department.deptHeadId” is not null, and the “Employee.departmentId” is also not null. So to get your first entry in the database you have to put made up data into at least one table. The purist in me doesn’t like doing that.
Thinking that you need a department before you can have an employee, I have allowed “deptHeadId” to have null values.
I’m glad it was helpful and thanks for the nice words. I understand your sentiments. It’s just a theoretical, easy-to-understand example. Tweak it as you like so that it feels more natural and logical to you.
Hi and thanks for your great tutorial series!
After reading all this and the Yii guide i found myself confused about the database naming conventions in the Yii Guide (http://www.yiiframework.com/doc/guide/basics.convention) it reads that “Both database tables and columns are named in lower case.” and that “Words in a name should be separated using underscores (e.g. product_order)”
Although you are using Employee -table name and firstName -column name. Shouldn’t these be employee and first_name according to the Yii conventions?
Thanks for your nice words and for the question. Honestly I think the conventions didn’t exist at first and have probably changed since I started working with Yii. While it’s best to go with conventions (and I normally name tables and columns in lowercase, too), all that really matters is that you be consistent and do what works best for you.
I do strongly disagree with the suggestion that words in a name be separated using underscores for this reason: in OOP it’s so common to be almost a rule that multiword functions, classes, and variables use camelcase (someFunction, someVariable, ThisClass). If you name the database column first_name, then you’ll later refer to $model->first_name, which is highly unconventional (as opposed to $model->firstName). Again, I’m of the opinion that conventions aren’t as important as consistency unless the framework insists upon certain conventions, which Yii does not, at least with respect to naming.
While I love to use camelCase convention (my primary specialization is Java
) wherever possible, I always use lower cae and underscores in databases. MS SQL and MySQL use both lower case and upper case for object names while some databases, for example Oracle, store all object names in upper case (no matter how you write them in your scripts). As a consequence, instead of veryLongAndImportantField you see VERYLONGANDIMPORTANTFIELD, which is VERY VERY inconvenient to say the least.
Yeah, I always thought that Oracle convention of all uppercase was terrible. Strange thing for perhaps the most dominant database application there is!
So many conventions out there, where to begin, lol.
I personally use underscores for varibles, so when I see
$model->first_name
I know it is a varible,data or other, and i’m not missing parentesis () for a method call.
But I think the bottomline is still – stay consistent.
I can see your thinking, but in my case, if I’m using an OOP language or approach, I’m likely to use camelcase for variables and methods: firstName and getFirstName(). If I’m using a non-OOP language or approach, I’m likely to use all lowercase and underscores: first_name and get_first_name(). So unless you mixed the two styles up–first_name and getFirstName()–there’s not really a clear distinction between a variable and a method call, correct?
Absolutely!
That has been in my experienced problem, mixing OOP with procedural code, and trying to rework or mix-in a coding convention to that legacy system.
I’m sure it is out of habbit I use that convention.
However, with reguard to database table/field names, I always use lowercase and underscores due to working between case-sensitive systems (windows/linux). I believe on windows, the default mysql install in case-insensitive, and linix it is case-sensitive(atleast that is true for imports). The problem comes when your writing sql queries, on windows everything works then once you push it to production you may notice that tbl_users is actually tbl_Users and nothing works – potential time saved is worth sticking with lower_case names for me.
I try to follow existing coding conventions of the platform I’m working on, and yii that would be $firstName and getFirstName().
I too had the error when trying to create the tables.
I altered the word KEY as described in reply#23 and both tables created ok. I then used the ALTER example in reply#23 to successfully alter the department table comment but I cannot get the employee comment to update with the same technique (nor even by directly editing the comment field with PhpMyAdmin.
Are these comments actually used by Yii – is that why they are important?
Thanks
Yes, the comments are used by Yii to pre-define the relations for you. They’re not imperative, but like most things, will save you a step later on.
I tried to make simple connection with SQL SERVER 2005 in Yii with syntaxmssql_connect(‘ANA-l4t0p’,'sa’,'XXXXXX’); and this is success, but when i get try with other way in main.php ‘db’=>array(
‘connectionString’ => ‘mssql:host=ANA-l4t0p;dbname=blog’,
‘username’ => ‘sa’,
‘password’ => ‘XXXXXX’,
‘charset’ => ‘utf8′,
‘tablePrefix’ => ‘tbl_’,
),
why came message error “CDbConnection failed to open the DB connection: SQLSTATE[HY000]: General error: 10007 ‘NAMES’ is not a recognized SET option. [10007] (severity 5) []“. Kindly for your help… I have tried with MySQL for both way before are success. Is my connectionstring of mssql is not correct?? Need you help.. Thank you
Unfortunately I don’t regularly use SQL Server (which is to say I haven’t touched it in years), so I don’t know what the proper DSN is for connecting to it.
Hi Larry, great Blog series!
I was wondering if you could help me out with understanding Yii Conditions and database query criteria…
Ie: model()->find(‘=?facebook_id’
what is all this =? =: nonsense?
Please helP!
Thanks
example, http://www.yiiframework.com/doc/api/CDbCriteria
Thanks for the nice words. This is a good question and I can see how it’d be confusing. What you are seeing is kind of like prepared statements (I say “kind of”, because I don’t know if Yii is treating them literally as prepared statements). So the criteria would be where some_filed=:thing, then you provide a value for :thing separately. When the query is run, :thing will be replaced with the actual value.
Hi
Simple tip to avoid MySQL error while creating employee and department tables.
replace single quotes with double quotes in comment.
as
COMMENT ‘CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)’
to
COMMENT “CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)”
Thanks for sharing, that’s excellent!
I believe the statement “The departmentId column will be a foreign key to the departments table” should have been:
“The departmentId column will be a foreign key to the Employee table”
No, no. The departmentId column in the Employees table is a foreign key to the Departments table. It’s correct as written.
Sorry for my question but I don’t know where I should put these tables definition (CREATE TABLE…)? Should I created any file schema? Because I’ve got a error “Table ‘Employee’ does not exist.”
My above post now is resolved, Now I know that first I should manually create these tables in database.
Yes, that’s correct. Glad you’ve got it now.
Question about your database table names:
Since the table is a collection of data, (i.e. more than one) I would prefer the table names to be plural, i.e. Employees, Departments
Is ther a reason you chose to use the singular tense (Employee) when naming the table?
Good question. The short answer is you should do whatever you prefer. The second answer is that I normally name my database tables in the plural as well, as they store multiple records. The third answer is that because an instance of a Model, such as Employee or Department will be singular, I might be inclined to use singular table names so that there’s a direct correlation between the two. The fourth answer is that I’m just not as consistent about such things as I’d like to be or ought to be. And the fifth and final answer is that I’m sometimes likely to provide multiple half-assed answers when I don’t have one good one!
Hi larry. I’m curious about the first table. Why do we need to separate the INDEX into 2? What will happen if we combine the 3 into 1 INDEX or if we don’t name the first INDEX?
like, INDEX (firstName, lastName, departmentID)
?
Thanks for the question. Just to be clear, what you’re asking about is a database design question, unrelated to Yii. Defining indexes is a complicated subject but the short answer is you wouldn’t combine all three fields into one index because no (or few) query would use those three fields together.
Hi Larry THANKS! Great tutorial posts.
I’m going through them and studying them and from subsequent pages it seems that the field name actually needs to be “id” for the primary key. You mention that this is Yii convention, but do parts of Yii assume that it is this way? I notice the line from a next installment regarding the relations for the employee model:
And wonder if the department relation assumes that the key that departmentID for the employee is being related to is the “id” field for the department. If Yii uses this as more than a convention but assumes (barring some override capability in the relation) that the primary key is “id” perhaps you might mention that this is an important convention in that parts of Yii are making this assumption?
It took me awhile to figure out how the relations work because of this but I think I’m starting to get it – just not sure if I’m making the right observation in this regard.
Thanks
Thanks, Mike, for the nice words and for the input. You make a good point about conventions. Yii, like all the frameworks I’ve worked with, has a “convention over configuration” approach, which is to say that if you do things Yii’s way, you don’t have to jump through any extra hoops. That being said, if, for example, you fail to name your column “departmentId”, then Yii may not automatically create the relation for you and you’ll need to add that code yourself. So, worst case scenario, failing to follow the conventions just means you’ll need to do a tad extra work later.
If one wants to use sqlite3 as database, the SQL statements look a little bit different:
CREATE TABLE Department (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name VARCHAR(40),
UNIQUE (name)
);
CREATE TABLE Employee (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
departmentId TINYINT UNSIGNED NOT NULL,
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
);
Thanks, Achim, for sharing that!
When I click on mysql_start it does not return the prompt.
I have no idea what you’re talking about.
Possible character set problem UTF (?) between browsers?
Both queries actually failed for me! I’m all set now, but the problem for me occurred when copying the Employee definition from Safari 5.1 (Mac) into the phpmyadmin sql text area for execution. When I got the error I noticed on the “comments” line were eight weird characters (  ) preceding the word “comment”. This page here “http://ask.metafilter.com/71246/What-the-” seems to shed some light on the issue of character sets and browsers.
Doing a little testing I found:
If I copy the query from Firefox 6.0.2 (Mac) into phpmyadmin, it works fine, but it definitely fails while copying from Safari for me. I mean I consistently get the error from Safari and I’m in California BTW.
The query also worked fine when I manually typed it in whether or not I used single or double quotes around the comment.
Thanks for the awesome articles, tips, books and insights! It’s all been a tremendous help to me.
Thanks for the input and thanks for the nice words!
Just pointing out that CRUD stands for Create (not Retrieve), Read, Update and Delete.
Not that it matters that much.
Great post!
Just pointing out that CRUD stands for Create, Read (not Retrieve), Update and Delete.
Not that it matters that much.
Great post!
Well, that’s not true, and even if it were true, there’s absolutely no semantic difference between “Read” and “Retrieve” nor “Delete” or “Destroy”. But thanks for the nice words regardless.
@Larry, it’s a great post and a great series.
Just wondering about 2 things, and wondering if you could clarify.
1. Are there any implications on the Model code if the schema doesn’t define explicit constraints (e.g. FK constraints were not supported in SQLite until recently) ?
2. How is one supposed to take care of evolutions in schema ? The Gii or yiic shell ways seems to require that you perfect the schema, but real life often requires refactoring, and in which case tools need round-trip engg capability.
Thanks for the nice words. As for #1, the only implication on the Model if you don’t indicate the constraint is that you need to add the relationship definition to the Model yourself. As for #2, I believe Yii now has a tool for implementing database changes, but I forget the name of it offhand.
Another thing… will the COMMENT regarding FK based relation to another table e.g. –
COMMENT “CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)”,
How does Gii tool (or any the Yiic shell) come to know of this comment and generate Model code accordingly ? Does Gii or Yiic necessarily have access to the SQL commands used to define the database schema ? My understanding (maybe wrong) was that they pick up that information by querying the database itself, and wondering if the comments are stored in database ?
Hello Larry
Many Thanks for great tutorials.
I have a real problem and seeking the best solution.
In this example Company, there will be many employees in each department “but each employee can be in one department up to 4 departments”.
also we do not need to define primary department for employees that makes the problem more simple.
What’s your opinion to best change in the “Employee” table?
Thanks for the nice words. In answer to your question, you’d remove the department reference from Employee and then create an intermediary table and Model: DepartmentsEmployees. Employee would then have a relationship to that Model.
Hey Larry,
Awesome Tutorial! I’m very new to all of this, and excuse me if my question is very amateurish or if I missed the answer in the text. When creating the tables using sql above in what file do I write that code. Index.php? Main.php? Some new file in the models folder?
Hey Larry,
I figured out the answer to my own dumb question, and finished this fantastic tutorial. Yii is amazing.
Do you happen to have any suggestions for resources/books/etc that could further assist a novice PHP developer with this framework and PHP OOP in general? Thanks.
Kudos for figuring it out and thanks for the nice words. As for resources, I haven’t read any Yii books, but two exist. You should read all the Yii documentation and extras at Yii’s site. For OOP, I do write about OOP in my PHP 5 Advanced book.
Hey Larry,
I’ve followed whole tutorial. where did you get “CONSTRAINT FOREIGN KEY (foreignKey) REFRENCES tablename(primaryKey)” phrase from?
actually I’m wondering if there is any syntax/phrase that we should be aware of.
that’s mysql stuff dude!
That syntax comes from the Yii manual and tells Yii how to create relations. You just need to change the “foreignKey”, “table name”, and “primaryKey” for your setup.