Guidelines for good schema design

Tips

1.1

database, schema design

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

<?php
 
$category = new Category();
 
// YUCK // BETTER $modelcategory->category_id    $model->id
 
$model$category->id
 
$category->category_name    $model$category->name $modelcategory->category_value    $model$category->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 nameBut 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 { // YESIt's very distracting to see the prefix everywhere in the code.

DO name eacha table's primary keyown ID column "id" ------------------------------------- Mostany tables will have a single unique primary keytheir 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.

Clearly this won't work for tabSometimes this rule doesn't apply, for examples with a composite primary key, but these should be relatively uncommon.hen 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)
~~~

 
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.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,);
~~~

 
This way, changing the person's username involves updating just this one record.

DO define foreign-key relationships in the database schemaDO name your foreign key fields ending in "id"
----------------------------------------------
Related to the prior note, if a 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 {$model->members

If you have to look it up, it makes the code much more difficult to read and maintain.
 
 
Translations
 
------------
 
 
- Chinese version: [Yii良好的架构(schema)设计指南](https://blog.csdn.net/cd_0227/article/details/50536390 "中文翻译")
 
- Russian version: [Руководство для проектирования хорошей схемы базы данных](http://phptime.ru/blog/yii/28.html)
 
- Japanese version: [DB スキーマ設計のガイドライン](https://qiita.com/items/63e68a0172a1d2f92b5c)