Difference between #7 and #6 of Guidelines for good schema design

unchanged
Title
Guidelines for good schema design
unchanged
Category
Tips
unchanged
Tags
database, schema design
changed
Content
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 your`category` 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.
~~~
[php]
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](http://www.yiiframework.com/doc/api/1.1/CArrayDataProvider#keyField-detail)
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:
~~~
[php]
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](http://www.yiiwiki.com/wiki/view/id/10/title/%E8%89%AF%E5%A5%BD%E7%9A%84%E6%9E%B6%E6%9E%84%28schema%29%E8%AE%BE%E8%AE%A1%E6%8C%87%E5%8D%97Chinese
version: [良好的架构(schema)设计指南](http://www.yiiwiki.com/6
"中文翻译")
- Russian version: [Руководство для проектирования
хорошей схемы базы
данных](http://phptime.ru/blog/yii/28.html)
- Japanese version: [DB
スキーマ設計のガイドライン](http://qiita.com/items/63e68a0172a1d2f92b5c)