Database object naming Reference

The article is a reference, not a norm, not a standard. It only represents my personal views and suggestions, and only consider the usual conditions of the rules, you can modify it according to the actual situation. Introduction

Code specification is an excellent programmer's necessary quality, however, many people pay attention to the program variables, methods, class naming, but ignore the same important database object naming. This article combined with a lot of technical articles and materials, as well as my own development experience, the database object naming rules put forward a little suggestion, I hope to provide you with some reference.

Note : Although this article is called "database object naming Reference", in fact, in this article I not only introduced the rules of database naming, associated with the database design and development of several issues to pay attention to. Basic naming Rules

table 1. Basic Database object naming

Database objects	Prefix	Example
Tables (table) Field (Column) Views (view) Stored Procedures (Stored procedure) Triggers (Trigger) Indexes (Index) Primary KEY (Primary key) Foreign key (Foreign key) Check constraint (check Constraint) Unique constraint User-defined datatype (user-defined data type) User-defined functions (user-defined function)	No No V pr Tr Ix_ Pk_ Fk_ Ck_ Uq_ Udt Fn	Student Title Vactivity Prdelorder Trorder_d Ix_customerid Pk_admin Fk_order_ordertype Ck_tablecolumn Uq_tablecolumn Udtphone Fnduedate

about naming Conventions

Variables (variables declared in T-SQL Programming), procedures (stored procedures or triggers, and so on), entities (tables, fields) should be named according to the entity meaning and process action they represent:

table 2. Good naming and bad naming examples

A good name	Bad name.
@CurrentDate @ActivityCount @EquipmentType Prcalculatetotalprice	@D @ActNum @ET @prRunCalc

Another common mistake is to use only computer-oriented terminology rather than business-oriented terminology, such as Processrecord is a vague name that should be replaced with a process business description, such as CompleteOrder.

Depending on the requirements of the previous article, the process name based on the business description may become lengthy, such as the following:

Prcounttotalamountofmonthlypayments (Calculates the total amount paid per month)

Prgetparentorganizationalunitname (get parent unit name)

You should consider using abbreviations at this point: if you can find a word abbreviation in the dictionary, use this as an abbreviation, for example: Mon (Monday), Dec (December) can remove the word vowel (except the initials) and the repeated letters of each word to abbreviate a word. For example: current = crnt, address = Adr, Error = Err, Average = AVG do not use ambiguous abbreviations (usually phonetic ambiguity). For example, B4 (before), XQT (execute), 4tran (Fortran) table, the name of the field: single table name, field name or plural list name, field name.

It may be very rare for everyone to consider whether to give the table a singular or plural name, such as a table that stores guest information, whether we should be customer or customers. I advocate singular table names, and here is a quote from the SQL Server 2000 book:

Advocates of a plural list say that a table is made up of a set of records, so a plural noun should be used to name it. The reason they are often used is that the Customer table is a collection of customers, and the collection means multiple, so they should be called customers tables. Unless you have only one client, you don't need a database in this situation.

According to the author's unofficial survey, 3/4 of SQL Server developers support the use of singular names. These developers believe that the Customer table is a collection of customers, not a collection of customers. A set of rows should not and will not be a collection of rows set, but will be called Row set (rowset). And, usually in the discussion, people use the singular name to address the table, saying that the customer table than the Customers table sounds clearer. Avoid unnecessary form suffixes

These two points I think we all know: 1, the table is used to store data information. 2, the table is a collection of rows. Then if the table name has been able to well describe the data information it contains, there is no need to add a suffix that represents the above two points.

In actual work, I saw some colleagues name the table: Guestinfo, which is used to store customer information. This name and the above mentioned 1th repeat, everyone knows that the table is stored information (information), and then add an info is tantamount to the superfluous, personally think that directly with the guest to do table name on it.

For the table that stores the flight information, he is named Flightlist. This name is repeated with the previous 2nd, the table is a collection of rows, then naturally is the list, plus the list suffix appears very superfluous, named Flight is not very good. Visible, he gave himself did not make a clear naming rules, otherwise the two tables must either be named: guestlist, flightlist either named Guestinfo, Flightinfo, not a mixture of the two. the naming of join tables in a many-to-many relationship

As you know, if you want to implement a many-to-many relationship between two entities, you need three tables, one of which is the parse table. Consider the following such a many-to-many relationship, which is a classic student selection problem: A student can choose many courses, a course can have many students. In order to achieve the above relationship, a parse table is needed (this table stores only the Student ID and course ID, and the students ' information and course information exist in their respective tables, the name of the table, the suggested wording is to merge the table names of two tables (if the table name is longer to simplify), here Studentcourse. The fields in this table are named StudentID, CourseID (both the composite primary key for this table, the foreign key for connecting the student table and the course table, etc.), and so on to the name of the primary key and the foreign key, so as to achieve a many-to-many relationship between the student and the class, of course , this relationship can add additional things, such as the Studentcourse table to add accesslevel fields, range d{read-only, complete, prohibit}, you can achieve access levels. the customary field name front/suffix

Database development for a long time, slowly will find out a rule: is a lot of fields have some common characteristics. For example, some of the fields are representative of time (such as posting time, comment time), some representative number (such as browsing number, comment number), some representative of the true and false type (for example, whether the blog essay displayed on the home page). For this same type of field, you should use a uniform prefix or suffix to identify it.

Let's give a few examples to see more clearly.

For everyone familiar with the forum, you need to record the last time the member logged in, when the General people will name this field logintime or Logindate. At this time, there has been a ambiguity: for another developer, if only look at the table's field name, not to look at the contents of the table, it is easy to logintime understand the number of logins, because, time also has a very common meaning, is the number of times.

In order to avoid this, it should be clear that all fields representing the time will be uniformly terminated by Date.

We often need to count the number of posts, the number of replies, and this is where developers usually name fields: Postamount, Posttime, Postcount, again, because of time ambiguity, we first eliminate the use of posttime as a field name. Next, Amount and count can indicate the meaning of the count, which is appropriate. Here, I recommend using count. Why, then? If you have done ASP development, I believe you must know RecordCount this attribute, the name of the time there is a principle: the use of conventional names, rather than to create a name . Since Microsoft uses the Count suffix to represent numbers, why don't we.

As a result, all fields that represent a number should end with count. This concept is done to promote, it is easy to draw, the number of browsing is viewcount, the number of logins for logincount and so on.

Another example, we rarely in the database directly save the image of binary data, usually only the URL path to save the picture, in the article management system, if it is reproduced articles, also used to record the source of the article field. The individual suggests that all fields that represent links are the end of the URL. as a result, the picture path field named ImageUrl, the article source field named sourceURL.

The last example, we often need to use Boolean, for example, this essay should not be shown to the home page, this essay is not saved to the draft box and so on. Similarly, according to Microsoft's recommendation, Boolean-type values begin with IS, has, or can.

If I were to build a field to indicate whether to put the essay on the first page, it must have the name: Isonindex

Similar examples are many, I just cite a few typical examples, we can expand on their own, if I can play a role in the very satisfied. A problem to note when naming a field

I've found that a lot of developers like to prefix fields with table names, for example, if a table is called user, he will name the fields in this table: UserId, UserPassword, UserName, Userphone, and so on. Personally, this is not necessary because you already know exactly what the table is storing the user's information, and the fields are necessarily for user. Also, your SQL code looks a little more streamlined in a join join operation, such as [User]. UserName = Aritcle.articleauthor Such code can be fully implemented as [User]. Name = Article.author.

There is also a special case where the foreign key of the table contains the fields. In this case, I tend to use table name +id way, such as CategoryID, USERID, etc. Assuming there is a table article, then its primary key I will name the ID, the associated user table The foreign key contains the field, I will name the UserID. This is because when you create an object in a language (such as C #), you sometimes use a code generator that generates the object's fields, property names based on the field name of the database, and the generated code is more structured. problems to be noted when building a table The database is not only used to save data, it should also be responsible for maintaining the integrity and consistency of the data

I've seen a lot of developers design databases that give me the feeling that the database works just like its name in their eyes-just for storing data, nothing but a primary key that has to be built ... There are no check constraints, no indexes, no foreign key constraints, no views, or even no stored procedures.

Here, I propose the following database design recommendations: If you want to write code to ensure that the rows in the table are unique, add a primary key for the table. If you want to write code to make sure that a separate column in the table is unique, add a constraint to the table. Add a CHECK constraint if you want to write code that determines that the values of the columns in the table can only belong to a range. If you want to write code to connect to the parent-child table, create a relationship. If you want to write code to maintain "once a row in the parent table changes, and then change related rows in the child table," Enable cascading deletes and updates. If you want to invoke a large number of joins to make a query, create a view. Use a stored procedure if you want to complete a business rule by writing a database-operation statement.

Note: Here I did not mention the trigger, practice proved that the trigger will make the database quickly become too complex, more importantly, the trigger is difficult to debug, if accidentally built a serial trigger, it is even more headache, so I prefer not to use triggers at all. to build a table with a thought of not null

I found that a lot of developers were building a new field when they were building it, and his idea was this: the default field is nullable, and then to determine if it is not NULL, if not, OK, this field can be null, and then proceed to the next field. The result is often a table. All fields except the primary key can be null.

The reason why there is such a way of thinking, is because the null good, the program is not easy to make mistakes Ah, you insert records if accidentally forgot to lose a field, the program can still run, and will not appear "xx field cannot be null" error message.

However, the result is very serious, it will make your program become more cumbersome, you have to do some meaningless null value processing to avoid the program error. To make matters worse, if some important data, such as a certain value of an order, is null, then you know that any value and NULL operation (such as subtraction) will result in null, resulting in the result that the total amount of the order is also null.

You can run the following code to try this:

Select Null + 5 as Result

You might say that even if I set the field to not Null, it can still accept an empty string, so that it still has to do with NULL in the program. Remember, the database also gives you a powerful weapon, a Check constraint, when you need to make sure that a field can be neither null nor empty, you can write this:

ColumnName Varchar (m) not Null Constraint ck_columnname Check (Len (ColumnName) > 0)

So, the logical way of thinking should be this: the default is not NULL, and then determine if the field is not NULL, if not, OK, this field is not null, the next field. An example script for building a table

I am building my own personal space, where the article table is written:

Create Table Article
(
Id Int Identity (1,1) not Null,
Title Varchar not Null Constraint uq_articletitle Unique,
Keywords Varchar (m) not Null,
Abstract Varchar not Null,
Author Varchar (m) not Null Default ' Zhang Ziyang ',
Type TinyInt not Null Default 0 Constraint ck_articletype Check (type in (0,1,2))--0, original; 1, compiled; 2, translation
Isonindex Bit not Null Default 1,--whether to display on the first page
Content Text not Null,
SourceCode Varchar (MB) Null,--Download path of program source code
SOURCE Varchar not Null Default ' tracefact '--origin of the article
Srcurl Varchar Null,--URL of the source of the article
Postdate DateTime not Null Default GetDate (),
Viewcount Int not Null Default 0,
ClassId Int not Null--The foreign key contains fields, article categories

Constraint pk_article Primary Key (ID)--Establish primary key
)

As you can see, here I use a Check constraint to make sure that the article type is only 0,1,2. Here, what I want to say is a CHECK constraint naming convention: Although a check constraint is for a field, it cannot have a check constraint of the same name in the same database. Therefore, it is recommended to use the Ck_ + table name + field name to name it, such as the ck_articletype in this example script.

In addition, I used a unique constraint to ensure the uniqueness of the title of the article. Since this is my blog post table, there should be no duplicate topics, which avoids inserting duplicate values when you use the INSERT statement. Similar to a check constraint, the naming convention here is: Uq_ + table name + field name. name of primary key

The primary key is named Pk_tablename, according to the default specification of SQL Server (the primary key name that is generated by default when the primary key is created using Enterprise Manager). The primary key is for a table, not for a field, and we sometimes meet in Enterprise Manager with a table of two fields preceded by a key icon (such as SQL Server 2000 with the Employeeterritories table of the Northwind sample database), Will mistakenly assume that the primary key is for the field, that is to say that a table has two primary keys, in fact, only a primary key, but contains two fields, which is often said compound primary key. For a more vivid understanding, look at the SQL statement that sets up the composite primary key, taking the Studentcourse multiple join table mentioned above as an example:

Alter Table Studentcourse
ADD Constraint pk_studentcourse Primary key (StudentID, CourseID)

Visible, for primary key Pk_studentcourse, contains two fields StudentID and CourseID. name of foreign key

The foreign key is named the table name of The table name _ foreign key referenced by the Fk_ foreign key. Because the table in which the foreign key resides is from the table, the top can be written as fk_ from the table name _ Main Table name .

The name of the field that the foreign key contains, and the field and foreign key contained in the foreign key are completely different concepts. The foreign key contains the name of the field, recommended as the table name + Id of the foreign key.

Consider such a relationship, table hotel, field ID, Name, Cityid. Table City, Field Id,name. Because a city may have many hotels, so it is a one-to-many relationship, town is the main table (1 side), hotel is from the table (many). In the hotel table, Cityid is used as a foreign key.

When implementing a foreign key, we can write this:

Alter Table Hotelinfo
Add Constraint fk_hotelinfo_city Foreign Key (Cityid) References City (ID)
On the Delete no action on Update no action

Obviously, Fk_hotelinfo_city is the name of the foreign key, Cityid is the name of the field that the foreign key contains.

Note: When you create a database table, you typically write three SQL script files. The first file contains only all the SQL statements that created the table, the CREATE TABLE statement. The second file contains the statement that deletes the relationship and the table, where all the statements that delete the relationship, that is, the drop CONSTRAINT statement is centered in the upper part of the file, all the statements that delete the table, the DROP TABLE statement, are concentrated in the lower half of the file. The third file contains statements that establish relationships between tables. This practice will be more convenient when you transplant the database, cause I do not explain, you try to know.

As for the fields that the foreign key contains for the parsing table in a many-to-many relationship, we can write this down (again, in the many to many examples of student selection):

Establish a foreign key relationship between the analytic table Studentcourse and the student table:

Alter Table Studentcourse
Add Constraint fk_studentcourse_student Foreign Key (studentid) References Student (Id)
On the Delete no action on Update no action

Establish a foreign key relationship between the analytic table Studentcourse and the course table:

Alter Table Studentcourse
Add Constraint fk_studentcourse_course Foreign Key (CourseID) References Course (Id)
the name of the on Delete no action on Update no action trigger

Consists of three parts: prefix (TR), which describes the type of database object. A basic section that describes the tables that are added by triggers. Suffixes (_i, _u, _d) that display the naming of stored procedures for modifying statements (Insert, update, and delete)

As you know, the prefix of the system stored procedure is sp_, in order to avoid confusing the user stored procedure with the system stored procedure, I recommend that you use PR as the name of the stored procedure you define.

At the same time, the name of the rule is: the use of Prgetitembyid name, such as:

Here, there is an interesting place to ponder. When we name the stored procedure according to the rules above, there are two ways: verbs to precede, nouns to put behind. Put the noun in front and the verb behind.

I personally recommend the use of Mode 2, now to say why:

Take Northwind For example, if you have 4 stored procedures for Employees tables, name them: Premployeeinsert, Premployeeupdate, Premployeedelbyid, Premployeegetbyid

At the same time for the Products table you have a similar 4 stored procedures, respectively, named: Prproductinsert, Prproductupdate, Prproductdelbyid, Prproductgetbyid

At this point, when you use Enterprise Manager to view, you will find stored procedures like the following neat arrangement:

Premployeedelbyid
Premployeegetbyid
Premployeeinsert
Premployeeupdate
Prproductdelbyid
Prproductgetbyid
Prproductinsert
Prproductupdate

It is easy to see that the more you have stored procedures, the more obvious the advantage of this naming method. naming parameters in stored procedures

The entry parameters in the stored procedure, I recommend the same field name, and here, suppose you want to write a stored procedure that updates the Northwind database Employees table (simplified), you can write this:

Create Procedure Premployeeupdatebyid
@EmployeeId Int,
@LastName NVarchar (20),
@FirstName NVarchar (10)
As
Update Employees Set
LastName = @LastName,
FirstName = @FirstName
Where
EmployeeId = @EmployeeId

If @ @error <> 0 or @ @RowCount = 0
Raiserror 16001 ' Update user failed ' summary

In this article, I first raised the issue of the lack of attention to the naming of database objects by developers, and then listed a summary of the names of the data objects.

Then I detailed the rules of database object naming in the order of tables, fields, primary keys, foreign keys, triggers, and stored procedures.

In the meantime, I've been interspersed with topics that are common in database development, including issues to be aware of when building tables, and tips to take when managing stored procedures.

I hope this article will help you.