Yii 1.1: Guidelines for good schema design

75 followers

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 yourcategory 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.

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 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:

-- 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:

-- 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:

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.

Total 15 comments

#18075 report it
Yatin Mistry at 2014/09/04 11:32am
Excellent article

Thanks for sharing this article.

And thanks for referencing giix.

#17906 report it
kuya1284 at 2014/08/09 04:08am
I agree with everything except for naming PK's simply using "id"

For me personally, while somewhat redundant, I find it more consistent to name Primary Keys as

  • If you have to write your own custom queries without using AR, you can easily avoid the use of aliases to prevent ambiguities. For example, instead of
SELECT u.id AS User_id, c.id AS Comment_id FROM User u JOIN Comment c ON u.id=c.user_id;

I could simply write

SELECT User_id, Comment_id FROM User u JOIN Comment c ON c.user_id=u.user_id;
  • It provides more clarity with the FK relationships when performing joins (or even complex joins). For example,
SELECT u.User_id, u.first_name, c.Comment_id, p.Post_id, p.Title FROM User u LEFT JOIN Comment c ON c.user_id=u.user_id INNER JOIN Post p ON p.post_id=c.post_id;

to me provides more clarity and consistency with FK and PK names, than

SELECT u.id AS User_id, u.first_name, c.id AS Comment_id, p.id AS Post_id, p.Title FROM User u LEFT JOIN Comment c ON c.user_id=u.id INNER JOIN Post p ON p.id=c.post_id;
  • When using AR, while $user->User_id may be ugly or redundant, I simply use $user->primaryKey instead.

Overall, it's just easier to see at a glance when the FK matches the PK of the target table.

#13102 report it
kavitama at 2013/05/04 10:35pm
So and So

I like the idea of convention over configuration but I think there is no FIXED rule over composite (or natural if you prefer) keys and surrogate keys.

I have been thinking and reading (http://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field) and a VERY GOOD framework (like I believe YII2 must be) has to permit some freedom in choosing both approaches and not limiting.

I am specifically referring to GII not supporting composite key CRUD.

#8986 report it
fsb at 2012/07/11 11:46am
Naming join tables (@karminski)

@karminski asked about naming what i call join tables. I like to use the same principles as for other tables: a name that expresses what each row of the table represents in the real world. In a join table each row is a relationship so the name would describe the relationship.

For example, naming the join table between your person and movie tables person_movie doesn't say much about what the relationship represents: did the person buy, watch, review, star in or direct the movie? So I prefer person_watched_movie (or whatever).

If, in addition to the FKs to the joined tables, the join table contains other information about the relationship then its name might reflect that content more than the relationship. For example, if the join relationship is "person reviewed movie" and the table contains the review text, star rating, date reviewed, etc. then review might be a better table name.

#8982 report it
karminski at 2012/07/11 09:47am
Many-to-many relationship table name

How do you name an associative table which is needed to break a many-to-many relationship into one-to-many relationships? Thanks for the tips!

#7299 report it
socialdev at 2012/03/12 10:55am
A must-read article

Thank you,

I immediately modified my sql file.

#5764 report it
fsb at 2011/11/10 01:45pm
Not happy with: DO name your foreign key fields ending in "id"

If table a has PK id and table b has a simple identifying relation to a (a has one b, b belongs to a), then b's PK should be a_id, which should also be its FK.

#5195 report it
Rodrigo Coelho at 2011/09/22 01:41am
Awesome

I wish I could upvote this more than once.

And thanks for referencing giix!

#5031 report it
dungdeveloper at 2011/09/07 08:58pm
I think the same ideas

Thank you for your good article!

#4925 report it
pligor at 2011/08/29 04:58pm
I couldn't agree more!

After less than an year working on Yii I have come up to the same conclusions as you! In fact my current project follows all of the above conventions and is working very well for me.

Very good article. Thank you!

In fact I would like to see implemented an IDE which enforces them (unless user wishes otherwise)

#4923 report it
andy_s at 2011/08/29 02:49pm
A note

Well, just my opinion... I prefer to name table fields with underscores, e.g. "user_id" instead of "userid". First, inside a database it looks better to me and second, sometimes it helps to distinguish table fields and class fields (named in CamelCase). Nice article :)

#4922 report it
opx at 2011/08/29 01:32pm
Cool

Nice tips. Thanks.

#4916 report it
phreak at 2011/08/29 02:43am
nice one

This looks as if I have written it :) Good job.

#4909 report it
Boaz at 2011/08/27 05:03pm
important read

Even if some recommendations are arguable, and I'm sure some would have different thoughts, this is a basic and important read for all.

Thanks for investing time in creating this!i

#4906 report it
lubosdz at 2011/08/27 02:08pm
Excellent article

Hi, this article summarizes up my experience gathered over few years working with various databases - mySQL, MSSQL, Oracle... Principles for good DB design that will save lots of time and refactoring efforts. Thanx. Lubos

Leave a comment

Please to leave your comment.

Write new article