1. DO name your database tables in the singular, not plural
2. DON'T prepend db table names to field names
3. DON'T include a table prefix in the model class name
4. DO name a table's own ID column "id"
5. AVOID semantically-meaningful primary key names
6. DO define foreign-key relationships in the database schema
7. DO name your foreign key fields ending in "id"
8. DO name relations to reflect their singular/plural nature

Virtually all Yii applications are built on top of a database, and though Yii is very flexible in how it addresses your DB, some design choices make things more convenient than others.

First, and most broadly, because Yii applications use ActiveRecord so heavily, design considerations revolve around optimization for that use, rather than for a human who will be composing complex SQL queries. In fact, many of these design notes are directly in conflict to best practices for creating SQL-friendly schemas.

But much of this is about creating code that can be read and understood by others, where naming conveys meaning, but where inconsistent conventions will make it much harder to follow.

This is especially the case if you're asking for help in the forums or in the #yii channel: using odd names that don't reflect good meaning gets you a lot more questions clarifying what the code is doing, and sometimes less help about your actual problem.

Consistency matters a lot.

However, these are only guidelines, not rules, and your code will work if they're not followed. But you'll have an easier path if you adopt them.

DO name your database tables in the singular, not plural ¶

Though we think of an SQL table as holding many of a thing, a model is just one of them: it just seems odd to see $model = new Comments(), and this oddness shows up again when defining relations, and elsewhere.

Call your table comment not comments, invoice not invoices, and so on, and reflect the model class names as well (Comment, Invoice, etc.)

If you absolutely cannot change the db schema, at least change the Yii model class name to reflect the proper case, but make an extra //COMMENT in the code to remind the user of this mismatch.

DON'T prepend db table names to field names ¶

This practice is common in traditional SQL schema design, but it's tedious when working with ActiveRecord. In yourcategory table, ~~~ -- NO -- YES create table category ( create table category(

category_id    INTEGER ...,        id    INTEGER ...,
category_name  VARCHAR ...,        name  VARCHAR ...,
category_value INTEGER             value INTEGER

); );

// YUCK // BETTER $model->category_id $model->id $model->category_name $model->name $model->category_value $model->value ~~~ Doing it the "long" way does make hand-crafted SQL queries a bit easier to read, but use in ActiveRecord less convenient.

DON'T include a table prefix in the model class name ¶

Yii supports the notion of table prefix, which is useful in shared hosting environments where all your applications share a single database. By prefixing your blog table names with blog_, your timekeeping application table names with time_, etc. they can all live in the same database without conflicting with each other.

The prefix tbl_ is commonly seen in many tutorials and samples.

But the classes should never contain these prefixes, because you don't have the same need to avoid conflicts: your blog application is different from your timekeeping application.

class TblComment extends CActiveRecord {       // NO
class Comment extends CActiveRecord {          // YES

It's very distracting to see the prefix everywhere in the code.

DO name a table's own ID column "id" ¶

Many tables have their own independent, single-column unique primary key (int NOT NULL AUTO_INCREMENT PRIMARY KEY is a common example), and things work a bit more smoothly if it is named id (not commentid or postid).

Though Yii figures out the primary key by reading the database schema no matter what you call it, other parts of the system may not be able to follow, and explicitly depend on the key being id.

Example: CArrayDataProvider assumes the key is id, and though you can override it with the keyField attribute, it's more convenient to avoid the need for it in the first place.

Sometimes this rule doesn't apply, for example when a table has a multi-column primary key or when a table's primary key is a foreign key to another table's ID.

AVOID semantically-meaningful primary key names ¶

A classic design mistake is creating a table with a primary key that has actual meaning. In this example, the user table makes the username the primary key: ~~~ [sql] -- don't do this! CREATE TABLE user (

name   VARCHAR(16) PRIMARY KEY,  -- bad idea
email  VARCHAR...
...

) ~~~ This presents two difficulties:

1. it's far less efficient to reference this field from others, because they contain 16 characters instead of a four-byte integer. This can be a real performance issue in a larger application with many references.
2. It's very difficult for a person to change his username if foreign-key constraints are enabled in this system: the table field and all the references would have to be changed at the same time, and this will be expensive if it's even possible.

Far better is to create an integral primary key and make the name unique: ~~~ [sql] -- much better CREATE TABLE user ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL UNIQUE, email ... ... ); ~~~ This way, changing the person's username involves updating just this one record.

DO define foreign-key relationships in the database schema ¶

Most databases allow the database to define relationships between tables, such that this field holds an ID pointing to the primary key of some other table. These are Foreign Keys, and help provide referential integrity by not allowing you to delete a row if somebody else points to it.

MySQL's InnoDB enforces foreign-key constraints, and though MyISAM allows you to define them, it won't enforce them. Yii knows how to read these relationships from the schema, and the Gii/Giix tools will create relations for you automatically.

But even without Yii considering them, foreign-keys are a vital part of maintaining referential integrity of your database; there are many tutorials on the web on how to learn about them.

DO name your foreign key fields ending in "id" ¶

Related to the prior note, if you have a field that holds the ID of a user, call the field userid rather than user. This is because for every foreign key you include in a table, you'll almost certainly wish to define a relation for it.

In Yii, class variables, db fields, virtual attributes, relations all share a single namespace, so it's not possible to have $model->user be both the foreign key in the table and the relation.

By calling the FK userid, the BELONGS_TO relation of $model->user forms naturally and easily:

class Post extends CActiveRecord {

   public function relations()
   {
       return array(
          'user' => array(self::BELONGS_TO, 'User', 'userid')
       );
   }

Note: some prefer to use Id or _id instead of id. This is strictly a matter of personal preference, but be consistent.

DO name relations to reflect their singular/plural nature ¶

Continuing our theme of consistency and making code easier to read by others, the relations ought to reflect their singular/pluralness in their names.

• HAS_ONE - returns a single model: singular
• BELONGS_TO - returns a single model: singular
• HAS_MANY - returns an array of models: plural
• MANY_MANY - returns an array of models: plural

Note that for relations returning an array, they might have only one model, but the fact that they're an array is what warrants the plural name.

You should always be able to tell just by looking whether a relation returns an array or a model:

$model->post
$model->comments
$model->author
$model->members

If you have to look it up, it makes the code much more difficult to read and maintain.

Chinese translation

Russian version: Руководство для проектирования хорошей схемы базы данных