Difference between #7 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
[...]
-----------------------------------------------
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 {
[...]
If you have to look it up, it makes the code much more difficult to read and maintain.

Translations
 
------------
 
 
- Chinese version: [Yii良好的架构(schema)设计指南](https://www.yiiwiki.com/6blog.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 630 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 #7 | Revert to #9

Revisions

View all history