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

Revision #9 has been created by softark on Aug 16, 2018, 3:00:25 PM with the memo:

Fixed marking ups
« previous (#8)

Changes

Title unchanged

Guidelines for good schema design

Category unchanged

Tips

Yii version unchanged

1.1

Tags unchanged

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
[...]
-----------------------------------------------
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
----------------------------------------------------------

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.
[...]
124 0
71 followers
Viewed: 123 624 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 #8 | Revert to #9

Revisions

View all history