Difference between #2 and #9 of
Guidelines for good schema design

Changes

Title unchanged

Guidelines for good schema design

Category unchanged

Tips

Yii version changed

1.1

Tags changed

database, schema design

Content changed

[...]
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 name
[...]
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 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 schema
[...]
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)
124 0
71 followers
Viewed: 123 621 times
Version: 1.1
Category: Tips
Tags: database, schema design
Written by: Steve Friedl
Last updated by: softark
Created on: Aug 27, 2011
Last updated: 6 years ago
Update Article
Revert to #2 | Revert to #9

Revisions

View all history