Perl Web App Development Tutorial-
& # 8250;
- here
Database table and field name naming conventions
At Mojolicious Startup, we want to show you one naming convention for database table names and field names for those who are starting a business.
You can freely decide the table name and column name of the database, but if you have one naming convention in your project, you can name it consistently and you will not have to worry about it.
I found the following article as a well-organized one, so I will translate it and introduce it. Yii is one of PHP's web frameworks.
Read Yii for Mojolicious and ActiveRecord for Perl's DBI or O / R mapper. I've added some supplements for Mojolicious startups.
- Name database tables in singular rather than plural
- Do not add DB table name before field name
- Do not include table prefix in model class name
- Name the ID column of the table "id"
- Avoid meaningful primary key names
- Define foreign key relationships in the database schema
- Name foreign key fields ending in "id"
- Perform name relationships that reflect the properties of the singular / plural
Introduction
Virtually all Yii applications are built on a database, and Yii is very flexible in how to deal with DBs, but good design choices make it more convenient than other designs.
First, and most broadly, Yii applications use ActiveRecord very often, so design considerations revolve around optimizing ActiveRecord usage rather than the person writing complex SQL queries. .. In fact, many of these design notes are in direct conflict with best practices for creating SQL-friendly schemas.
However, most of these best practices are about writing code that others can read and understand. Naming conveys meaning, but inconsistent rules make it much harder to follow.
This is especially true if you are seeking help on the forums or the #yii channel. Using strange names that don't reflect good meanings gives you many questions that clarify what your code is doing. Your actual problem.
Consistency is important.
However, these are just guidelines, not rules, and your code will work if you don't follow them. But adopting them opens up an easier path.
Supplement
ActiveRecord is the default O / R mapper included with Yii. For Mojolicious Startop, read Perl's DBI or O / R Mapper.
Name database tables singular instead of plural
SQL tables are likely to hold many things, but the model is just one of them. It seems strange to see $model = new Comments (). This strangeness reappears, such as when defining relationships.
Call the table "comments" instead of "comments" and "invoices" instead of "invoices". It will be reflected as it is in the Dell class name (comment, invoice).
If you can never change the DB schema, at least change the Yii model class name to reflect the appropriate case, but add extra comments to the code to make the user aware of this discrepancy.
Supplement
The point is to make the table name singular.
comment # OK comments # Not OK invoice # OK invoices # Not OK
Do not add the DB table name before the field name
This method is common in traditional SQL schema design, but it can be tedious when using ActiveRecord. An example of a category table.
--NO --YES
create table category (create table category (create table category (create table category)
category_id INTEGER ..., id INTEGER ...,
category_name VARCHAR ..., name VARCHAR ...,
category_value INTEGER value INTEGER
););
<? php
$category = new Category ();
// YUCK // BETTER
$category->category_id$category->id$category->category_name$category->name$category->category_value$category->value</pre>
The "long" way makes handmade SQL queries a little easier to read, but it's not very convenient to use with ActiveRecord.
<b> Supplement </b>
The point is that you don't precede the field name with the table name.
<h3> Do not include the table prefix in the model class name </h3>
Yii supports the concept of table prefixes. This is useful in a shared hosting environment where all applications share a single database. You can store them all in the same database without conflict, such as by adding blog_ to the table name of the blog and time_ to the table name of the timekeeping application.
The prefix tbl_ is common in many tutorials and samples.
However, you cannot include these prefixes in your class because the need to avoid conflicts is not the same. Blog applications are different from time management applications.
<pre>
class TblComment extends CActiveRecord {// NO
class Comment extends CActiveRecord {// YES
Seeing the prefix everywhere in the code is very distracting.
Supplement
This is a description of ActiveRecord classes, so if you give Perl a model name that corresponds to a table name, it means that you don't include the table prefix.
Name the ID column of the table "id"
Many tables have their own independent, single-column, unique primary key (int NOT NULL AUTO_INCREMENT PRIMARY KEY is a common example), and for the name id (not commentid or postid), it's a bit smoother. It works.
Yii reads the database schema to determine the primary key, but the rest of the system cannot follow and cannot explicitly depend on the key that is the id.
Example: CArrayDataProvider assumes that the key is id. You can override it with the keyField attribute, but it's better to avoid needing it from the beginning.
For example, if a table has a multi-column primary key, or if the table's primary key is a foreign key to the ID of another table, this rule does not apply.
Supplement
The point is that the ID that identifies the row should be named id.
Avoid meaningful primary key names
A classic design mistake is to create a table with a primary key that has real meaning. In this example, the user table uses the user name as the primary key.
--Don't do it like this
CREATE TABLE user (
name VARCHAR (16) PRIMARY KEY, --bad idea
email VARCHAR ...
...
)
This has two drawbacks.
It's much more efficient to reference this field from other fields because it consists of 16 characters instead of a 4-byte integer. This can be a real performance issue for large applications with many references.
It is very difficult for a user to change a user name if foreign key constraints are enabled on this system. You need to change the table fields and all references at the same time, which is costly if possible.
Much better is to create an essential 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 ... ... );
With this method, if you change an individual's username, only this one record will be updated.
Supplement
The point isLet's have an id just to identify the row without taste, and make it an auto-increment with the primary key. Set a unique constraint on the meaningful primary key (name in this example).
Define foreign key relationships in the database schema
In most databases, the database can define relationships between tables, so this field holds an ID that points to the primary key of another table. These are foreign keys and provide referential integrity by preventing rows from being deleted if someone else points to them.
MySQL's InnoDB enforces foreign key constraints. MyISAM allows you to define them, but it doesn't enforce them. Yii knows how to read these relationships from the schema, and the Gii / Giix tool will automatically create the relationships.
But even if Yii doesn't consider them, foreign keys are an important part of maintaining database referential integrity. There are many tutorials on the web on how to learn about them.
Supplement
The point is that if you want to put a foreign key constraint in the database schema, put it on the database side, not on the O / R mapper side.
Name foreign key fields ending in "id"
In connection with the previous note, if there is a field that holds the user's ID, call the userid field instead of user. This is because we almost certainly want to define a relation for every foreign key we include in the table.
In Yii, class variables, DB fields, virtual attributes, and relationships all share a single namespace, so $model->usercannot be both a table foreign key and a relationship.
By calling the FK user ID, the BELONGS_TO relationship of $model->useris formed naturally and easily.
class Post extends CActiveRecord {
public function relations ()
{
return array (
'user' => array (self::BELONGS_TO,'User','userid')
);
}
Note: Some people prefer to use Id or _id instead of id. This is strictly a matter of personal taste, but it needs to be consistent.
Supplement
The point is that foreign key names should be followed by "id" or "Id" or "_id" to ensure consistent naming conventions. For Mojolicious startups, we'll use the method of prefixing the table name with "_id" as the name of the foreign key.
Perform name relationships that reflect singular / plural properties
Relationships should reflect the singular / plural in the name in order to continue the theme of consistency and make the code easier for others to read.
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
For relations that return an array, there may be only one model, but keep in mind that the fact that it is an array guarantees multiple names.
You should always be able to tell if the relation returns an array or a model.
$model->post$model->comments$model->author$model->members</pre> The code is much harder to read and maintain if you need to look it up. <b> Supplement </b> This is a story about method name naming conventions when defining ActiveRecord one-to-one, one-to-many, many-to-one, and many-to-many. If Perl's O / R mapper had similar functionality, you can refer to it.