Sqldatabase object naming detailed document

Document directory

• Singular table name, field name, plural table name, and field name?
• Avoid unnecessary table suffixes
• Name of the connected table in multiple-to-multiple relationships
• Conventional field name prefix/suffix
• Notes for field naming
• The database is used not only to store data, but also to maintain data integrity and consistency.
• Create a table using Not Null
• Example script for table Creation
• Name of parameters in Stored Procedures

For a large project, designing naming rules for databases is an important part. Good table design can make people look comfortable and understand what it means at a glance, the following is a good reference document for naming database objects.

Introduction

Coding standards are essential for a good programmer. However, many people pay great attention to the naming of variables, methods, and classes in the program, but ignore the naming of database objects that are equally important. This article, combined with many technical articles and materials and my own development experience, provides some suggestions on naming rules for database objects, hoping to provide some reference for you.

NOTE:Although this article is named "database object naming reference", in fact, this article not only introduces database naming rules, this section describes several issues that need to be paid attention to during database design and development.

Basic naming rules

Table 1. Basic database object naming

Database objects	Prefix	Example
Table) Field (column) View) Stored Procedure) Trigger) Index) Primary Key) Foreign key) Check Constraints) Unique constraints User-Defined Data Type) User-Defined Functions)	None None 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

Naming Conventions

Variables (variables declared in T-SQL programming), procedures (stored procedures or triggers, etc.), entities (tables, fields) should be named based on the meaning of the entity they represent and the role of the process:

Table 2. Good naming and bad naming examples

Good name	Bad Name
@ Currentdate @ Activitycount @ EquipmentType Prcalculatetotalprice	@ D @ Actnum @ Et @ Prruncalc

Another common mistake is to use only computer-oriented terms instead of company-oriented terms. For example, ProcessRecord is a vague name and should be replaced by a process business description, for example, CompleteOrder.

If you fully comply with the previous requirement, the process name described based on the business may become lengthy, for example:

PrCountTotalAmountOfMonthlyPayments (calculates the total amount paid per month)

PrGetParentOrganizationalUnitName (obtain the name of the upper-level organization)

In this case, you should consider using the abbreviation:

• If you can find the abbreviation of a word in the dictionary, use it as the abbreviation, for example: Mon (Monday), Dec (December)
• You can delete the vowels (except the first letter) of a word and the repeated letters of each word to abbreviation a word. For example: Current = Crnt, Address = Adr, Error = Err, Average = Avg
• Do not use abbreviations with different differences (generally voice ambiguity ). For example, b4 (before), xqt (execute), 4 tran (Fortran)

Table and field naming: singular table name, field name or plural table name, field name?

Maybe we seldom consider whether to give the table name a singular or a plural number. For example, for a table that stores the guest information, should we assume "Customer" or "MERs? I advocate the singular table name. The following is a reference from SQL Server 2000:

The camp that advocates the use of the plural table name thinks that a table is composed of a group of records, so it should be named by the plural. The reason they often use is that the customer table is a set of Customers, and the set means multiple, so they should be called the MERs table. Unless you only have one customer, you do not need a database in this case.

According to my informal survey, 3/4 of SQL Server developers support singular naming. These developers believe that the customer table is a collection of customers, rather than a collection of customers. A group of rows should not and will not become a rows set (a set of rows), but will be called a row set ). In addition, during the discussion, we usually use the singular name to name a table. The Customer table sounds clearer than the MERs table.

Avoid unnecessary table suffixes

I want to know these two points: 1. Tables are used to store data information. 2. A table is a set of rows. If the table name can properly describe the data information contained in the table, you do not need to add any suffixes that reflect the above two points.

In actual work, I see some colleagues name a table like this: GuestInfo, used to store customer information. This name is the same as the 1st point mentioned above. Everyone knows that the table is originally used to store information, and adding Info is nothing more than a superfluous addition. I personally think it is enough to directly use Guest as the table name.

The table that stores flight information is also named flightlist. This name is the same as the 2nd point mentioned previously. If a table is a set of rows, it is actually a list, and the suffix of The list appears many times. Isn't it good to name it flight? It can be seen that neither of them has a clear naming rule. Otherwise, the two tables must be named either guestlist, flightlist, or guestinfo or flightinfo, instead of mixing the two.

Name of the connected table in multiple-to-multiple relationships

As you know, to implement multi-to-many relationships between two entities, you need three tables, one of which is a parsing table. Consider the many-to-many relationship below. This is a typical course selection problem for students: a student can choose many courses, and a course can have many students. In order to achieve the above relationship, a parsing table is required (this table only stores the student ID and course ID, and the student information and course information are in the tables of each student ),We recommend that you name the table by combining the two table names (which can be simplified if the table name is long). studentcourse is used here.The fields in this table are named studentid and courseid (both the composite primary key of this table and the foreign key connecting the student table and the course table respectively, wait until the names of the primary key and the foreign key are added. This achieves many-to-many relationships between the student and the course. Of course, you can add additional things to this relationship, for example, adding the accesslevel field to the studentcourse table and the Value Field d {read-only, complete, forbid} can achieve the access level.

Conventional field name prefix/suffix

After a long time of database development, we will gradually explore a rule that many fields share some common features. For example, some fields represent the time (such as the posting time and comment time) and some represent the number (such as the number of views and comments ), some are true and false (for example, whether to display a blog on the home page ). For this type of field, you should use a uniform prefix or suffix to identify it.

Let's take a few examples to better understand.

In a forum that everyone is familiar with, it is necessary to record the last logon time of a member. At this time, most people will name this field logintime or logindate. At this time, there has been an ambiguity: For another developer, if you only look at the table field name and do not look at the table content, it is easy to understand logintime as the number of logins, because, time also has a very common meaning, that is, the number of times.

In order to avoid this situation, it should be clear that:All fields that represent time end with date.

We often need to count the number of posts and replies. At this Time, developers usually name the fields PostAmount, PostTime, and PostCount. In the same way, due to Time ambiguity, we will first exclude the absence of PostTime as the field name. Next, both Amount and Count can indicate the meaning of Count. Which one is suitable? Here, Count is recommended. Why? If you have done Asp development, I believe you must know the RecordCount attribute. There is a principle in naming:Use a common name instead of creating a name.. Since Microsoft uses the Count suffix to indicate the number, why not?

So,All fields that indicate numbers should end with count.To promote this concept, it is easy to conclude that the number of views is ViewCount, and the number of logins is LoginCount.

For another example, we seldom directly store binary data such as images in the database. Generally, we only store the URL path of images. In the article management system, if we repost an article, fields that record the source of the article are also used.It is recommended that all fields representing links end with the URL.Therefore, the field of the image path is named ImageUrl, and the source field of the article is named SourceUrl.

In the last example, we often need to use boolean values. For example, whether this essay is displayed on the home page or whether it is saved in the draft box or not.Similarly, according to Microsoft's suggestion, Boolean values start with is, has, or can.

If I want to create a field that indicates whether to place the content on the home page, its name must be as follows: IsOnIndex

There are a lot of similar examples. Here I just give you a few typical examples. You can expand on your own. If I can play a very interesting role, I will be satisfied.

Notes for field naming

I found that many developers like to add a table name to a field as its prefix. For example, if a table is named User, it will name the field in the table as follows: userId, UserPassword, UserName, UserPhone, and so on. In my opinion, this is unnecessary, because you already know exactly that the table stores the User information, and the fields in the table must be for the User. In addition, in the Join operation, your SQL code looks more streamlined, such as [User]. userName = Aritcle. code such as ArticleAuthor can be fully implemented as [User]. name = Article. author.

A special case exists here, that is, the fields contained by the foreign key of the table. In this case, I tend to use the table name + ID method, such as CategoryId and UserId. Suppose there is a table Article, then its primary key will be named as Id, and the fields contained in the foreign key associated with the User table will be named UserId. This is because sometimes code generators (generate object fields and attribute names based on database Field Names) are used to create objects in languages (such as C ), the generated code is more regular.

When creating a table, you must note that the database is not only used to store data, but also responsible for maintaining data integrity and consistency.

I have read a lot of databases designed by developers, and they feel that in their eyes, Databases Act like their names-they are only used to store data, nothing except the primary key that has to be created... 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 suggestions:

1. If you want to write code to ensure that the rows in the table are unique, add a primary key to the table.
2. If you want to write code to ensure that a single column in the table is unique, add a constraint to the table.
3. If you want to write code to determine that the values of columns in the table can only belong to a certain range, add a check constraint.
4. If you want to write code to connect to the parent-child table, create a link.
5. If you want to write code to maintain "when a row in the parent table changes, the related rows in the child table are changed", cascading deletion and update are enabled.
6. If you want to call a large number of joins for a query, create a view.
7. If you want to write database statements one by one to complete a business rule, you can use the stored procedure.

Note:I did not mention the trigger here. Practice has proved that the trigger will make the database quickly become too complex. More importantly, it is difficult to debug the trigger. If a trigger is accidentally created, it will be even more troublesome, therefore, I prefer not to use triggers at all.

Create a table using Not Null

I found that many developers want to create a new field when creating a table. The idea is as follows: by default, this field can be Null, then, judge whether it is Not Null or Not. If Not, OK. The field can be Null, and then proceed to the next field. The result is that all fields except the primary key of a table can be Null.

The reason for this is that Null is good and the program is not prone to errors. When you insert a record, if you accidentally forget to lose a field, the program can still Run, the error message "XX field cannot be Null" does not appear.

However, the result is very serious, and it will make your program more complicated. You have to handle unnecessary null values to avoid program errors. Worse, if some important data, such as saying that a certain value of an order is Null, then we all know that the result of any operation with Null values (such as addition, subtraction, multiplication, division) is Null, the result is that the total order amount is Null.

You can run the following code to try it:

Select Null + 5 As Result

You may say that even if I set the field to Not Null, it can still accept Null strings, so that the program still needs to process Null values. Don't forget, the database also gives you a powerful weapon, that is, the Check constraint. When you need to ensure that a field cannot be Null or empty, you can write it like this:

ColumnName Varchar (50) Not Null Constraint ck_ColumnName Check (Len (ColumnName)> 0)

Therefore, the rational way of thinking should be like this: by default, this field is Not Null, and then judge whether this field is Not Null. If Not, OK, this field is Not Null and is used as the next field.

Example script for table Creation

I am creating my own personal space. The article table is written as follows:

Create Table Article
(
Id Int Identity (1, 1) Not Null,
Title Varchar (50) Not Null Constraint uq_ArticleTitle Unique,
Keywords Varchar (50) Not Null,
Abstract Varchar (500) Not Null,
Author Varchar (50) Not Null Default 'zhang Ziyang ',
Type TinyInt Not Null Default 0 Constraint ck_ArticleType Check (Type in (, 2), -- 0, original; 1, compilation; 2, Translation
IsOnIndex Bit Not Null Default 1, -- whether to display on the homepage
Content Text Not Null,
SourceCode Varchar (100) Null, -- Download path of the program source code
Source Varchar (50) Not Null Default 'tracefact', -- Source of the article
SrcUrl Varchar (150) Null, -- URL of the document source
PostDate DateTime Not Null Default GetDate (),
ViewCount Int Not Null Default 0,
ClassId Int Not Null -- field contained in the foreign key, article category

Constraint pk_article primary key (ID) -- create a primary key
)

As you can see, here I use the check constraint to ensure that the document type can only be 0, 1, or 2. Here, I want to talk about the naming rules of the check constraint: although the check constraint is for fields, there cannot be a check constraint with the same name in the same database. Therefore, we recommend that you use CK _ + Table name + field name to name it, for example, ck_articletype in this sample script.

In addition, I also use the unique constraint to ensure the uniqueness of the article title. Because this is my blog article table, duplicate questions should not appear, which can avoid inserting duplicate values when using insert statements. Similar to the check constraint, the naming rule is uq _ + Table name + field name.

Primary Key name

According to the default specification of SQL Server (the default primary key name generated when the enterprise manager is used to create a primary key), the primary key is named pk_tablename. The primary key is for a table, not for a field, sometimes, you will see a key icon in front of two fields in a table in the Enterprise Manager (for example, the employeeterritories table in the northwind Sample Database of SQL Server 2000 ), it will mistakenly assume that the primary key is for the field, that is, a table has two primary keys, and the actual error is that there is only one primary key, but it contains two fields, this is the compound primary key. For a more vivid understanding, let's take a look at the SQL statement for creating a composite primary key. The many-to-many connection table studentcourse mentioned above is used as an example:

Alter table studentcourse
Add constraint pk_studentcourse primary key (studentid, courseid)

It can be seen that the primary key pk_studentcourse contains two fields: studentid and courseid.

Foreign key name

The foreign key is namedName of the table where the FK _ foreign key is located _ name of the table referenced by the foreign key. Because the table where the foreign key is located is a slave table, you can write itFK _ slave table name_master table name.

The names of fields contained in Foreign keys. The fields contained in Foreign keys are completely different from those contained in Foreign keys. The name of the field contained in the foreign key. It is recommended that:The name + ID of the table where the foreign key is located.

Consider such a relationship: the table Hotel, field Id, Name, CityId. Table City, field Id, Name. Because a City may have many hotels, there is a one-to-many relationship. The City is the main table (one-party) and the Hotel is the slave table (multiple parties ). In the Hotel table, CityId is used as a foreign key.

When implementing the foreign key, we can write as follows:

Alter Table partition info
Add Constraint fk_HotelInfo_City Foreign Key (CityID) References City (ID)
On Delete No Action On update No Action

Obviously, fk_hotelinfo_city is the name of the foreign key, and cityid is the name of the field contained in the foreign key.

Note:When creating a database table, you generally need to write three SQL script files. The first file contains only the SQL statements for creating tables, that is, the Create Table statement. The second file contains the statements for deleting a link and a table. Among them, all the statements for deleting a link, that is, the Drop Constraint statement, are concentrated in the upper part of the file and all the statements for deleting a table, the Drop Table statement is concentrated in the lower half of the file. The third file contains the statement for establishing the relationship between tables. This method will lead to great convenience when you port the database. I will not explain the reason. You will know it in a try.

For the fields contained in the foreign key of the parsed table in the many-to-many relationship, we can simply push them down and write it like this (return to the multi-to-many example of the Student Course Selection again ):

Create a foreign key relationship between the parsing Table StudentCourse and the Student table:

Alter Table StudentCourse
Add Constraint fk_StudentCourse_Student Foreign Key (StudentId) References Student (Id)
On Delete No Action On Update No Action

Create a foreign key relationship between the parsing Table StudentCourse and the Course table:

Alter Table StudentCourse
Add Constraint fk_StudentCourse_Course Foreign Key (CourseId) References Course (Id)
On Delete No Action On Update No Action

Trigger name

It consists of three parts:

1. The prefix (TR) describes the type of the database object.
2. Describes the tables added to the trigger.
3. Suffix (_ I, _ u, _ d), displays the modify statements (insert, update, and delete)

Name of a stored procedure

As you know, the prefix of the system stored procedure is sp _. To avoid confusion between the user stored procedure and the system stored procedure, we recommend that you use pr as the name of your own stored procedure.

At the same time, the naming rule is: Use a self-explanatory name, for example, prGetItemById.

Here, there is something interesting to think deeply about. You can use either of the following methods to name a stored procedure:

1. Place verbs in front and nouns in back.
2. Place Nouns in front and verbs in back.

I personally recommend using method 2. The reason is as follows:

Take the NorthWind as an example. Assume that you have four stored procedures for the Employees table: prEmployeeInsert, prEmployeeUpdate, prEmployeeDelById, and prEmployeeGetById.

For the Products table, you have four similar stored procedures named prProductInsert, prProductUpdate, prProductDelById, and prProductGetById.

When you use the Enterprise Manager to view the data, you will find that the stored procedure is neatly arranged as follows:

PrEmployeeDelById
PrEmployeeGetById
PrEmployeeInsert
PrEmployeeUpdate
PrProductDelById
Prproductgetbyid
Prproductinsert
Prproductupdate

It is easy to find that the more stored procedures you use, the more obvious the advantages of this naming method.

Name of parameters in Stored Procedures

The entry parameters in the stored procedure are recommended to be the same as the corresponding field names. Here, we assume that you want to write a stored procedure (simplified) for updating the Employees table of the northwind database. You can write this as follows:

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 'user update failed'

Summary

In this article, I first raised the issue that developers do not pay enough attention to database object naming, and then listed a simple table for naming data objects.

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

In the meantime, I also introduced some common issues in database development, including the issues that need to be paid attention to during table creation and the skills that can be used to manage stored procedures.