Database object naming Reference

Introduction

Coding specification is an essential quality of a good programmer, however, many people pay great attention to the name of variables, methods and classes in the program, but ignore the same important database object naming. This article combined with a number of technical articles and materials, as well as my own development experience, on the database object naming rules made a bit of advice, I hope to provide some reference for you.

Note: Although this article is called "database object naming Reference", in fact, in this article not only introduces the rules of database naming, but also describes the database design and development in the need to pay attention to several issues.

Basic naming Rules

Table 1. Base Database object naming

Database objects	Prefix	Example
Tables (table) Field (Column) Views (view) Stored Procedures (Stored procedure) Trigger (Trigger) Indexing (Index) Primary KEY (Primary key) Foreign key (Foreign key) Check constraint (check Constraint) Unique constraint User-defined data type (user-defined) 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, etc.), 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 naming.
@CurrentDate @ActivityCount @EquipmentType Prcalculatetotalprice	@D @ActNum @ET @prRunCalc

Another common mistake is to use only computer-oriented terminology, not business-oriented terminology, such as Processrecord, which is a vague name, and 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 per month paid)

Prgetparentorganizationalunitname (get parent organization name)

You should consider using abbreviations at this point:

• If you can find the abbreviation of a word in the dictionary, use this as an abbreviation, for example: Mon (Monday), Dec (December)
• You can delete the word vowel (except for the first letter of the word) and repeat the letter for each word to abbreviate a word. For example: current = crnt, Address = Adr, Error = Err, Average = AVG
• Do not use a conflicting abbreviation (usually a phonetic ambiguity). such as B4 (before), XQT (execute), 4tran (Fortran)

names of tables and fields:single table name, field name or plural list name, field name?

It may be very rare to consider the name of the singular or plural, for example, to store the guest information table, we should be customer, or customers? I stand for the singular table name, and here is a quote from SQL Server 2000 Bible:

A faction that advocates the use of plural table names says that a table is made up of a set of records, so you should name it with a plural noun. 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 the Customers table. Unless you have only one customer, you don't need a database in this case.

According to the author's informal survey, 3/4 of SQL Server developers support the use of singular naming. 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 the rows set (a collection of rows), 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 sounds clearer than the Customers table.

Avoid unnecessary table suffixes

These two points I think we all know: 1, the table is used to store data information. 2. A table is a collection of rows. Then if the table name is already good enough to describe the data it contains, there is no need to add a suffix that reflects the above two points.

In the actual work, I saw some colleagues named the table: Guestinfo, used to store customer information. This name and the above said the 1th repetition, who all know that the table is stored information (information), and then add an info is no more than the lily, personally think directly with guest to do the table name on it.

For the table that stores flight information, he is also named Flightlist. This naming again with the previous said the 2nd phase repeats, the table is a collection of rows, then the natural is the list, plus list suffix appears redundant, named Flight is not very good? It can be seen that he gave himself no clear naming rules, otherwise the two tables must be either named: Guestlist, flightlist or named Guestinfo, Flightinfo, and not a mixture of the two.

Naming of join tables in many-to-many relationships

As you know, if you want to achieve a many-to-many relationship between two entities, you need three tables, one of which is the parse table. Consider the following a many-to-many relationship, this is a classic student selection problem: A student can choose a lot of courses, a course can have many students. In order to achieve the above relationship, you need a parse table (this table only stores the student ID and course ID, and the student's information and course information exist in their respective tables), the name of the table, the recommended wording is to merge the table names of the two tables (if the table name is longer can be simplified), here as Studentcourse. The fields in this table are named StudentID, CourseID (both the composite primary key for this table, the foreign key for the connection student table and the course table, and so on to the name of the primary key and foreign key), thus achieving a many-to-many relationship between the student and the curriculum, of course , this relationship can also add extra things, such as to the Studentcourse table in the Add Accesslevel field, the range d{read-only, full, prohibit}, you can achieve access level.

pre/suffix of the customary field name

Database development for a long time, and slowly will find a rule: is a lot of fields have some common characteristics. For example, some fields represent time (for example, posting time, comment time), some represent number (e.g., number of views, number of comments), and some represent the true or false type (e.g. whether to display blog essays on the homepage). 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, it is necessary to record the last time the member logged in, the average person will be named this field Logintime or Logindate. At this time, there has been a ambiguity: for another developer, if you look only at the table field names, not to see the contents of the table, it is easy to logintime to understand the number of logins, because, time has a very common meaning, is the number of times.

To avoid this, it should be clear that all fields that represent time are uniformly terminated with date.

We often need to count the number of posts, replies to the number of information, at this time, developers usually go to name the field: Postamount, Posttime, Postcount, again, because of the ambiguity of time, we first eliminate the use of Posttime as the field name. Next, both Amount and count can represent the meaning of the count, which is appropriate? Here, I recommend using count. Why is it? 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 the number, why don't we?

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

For one more example, we seldom store binary data such as images directly in the database, usually only the URL path to save the image, and in the article management system, if it is reproduced, it will also use the field of the source of the record article. the individual suggests that all fields that represent links are the end of the URL. as a result, the field of the picture path is named ImageUrl, and the source field of the article is named sourceURL.

The last example, we often need to use the Boolean value, for example, this essay should not be displayed to the homepage, this essay is not saved to the draft box and so on. Similarly, as suggested by Microsoft, the values for Boolean types start with IS, have, or can.

If I were to build a field that would indicate whether or not to put an essay on the first page, it must have this name: Isonindex

Similar examples are many, I just cite a few typical examples here, we can expand on their own, if I can play a role is very satisfied.

an issue to be aware of when naming a field

I've found a lot of developers who like to prefix a field with a table name, for example, if a table is called user, then he will name the fields in this table: UserId, UserPassword, UserName, Userphone, and so on. Personally, this is not necessary, because you already know that the table is stored in the user's information, then the field must be for the user. Also, your SQL code will look more streamlined in the join connection 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 table's foreign key contains the fields. In this case, I prefer to use the table name +id way, such as CategoryId, UserId, and so on. Assuming that there is a table article, then its primary key I will name as ID, associated with the user table of the foreign key containing the field, I will name the UserID. This is because when you create an object in a language (such as C #), you sometimes use the code generator, which generates the field, property name, of the object based on the field name of the database, and the resulting code is more structured.

issues to be aware of when building a tableThe database is not only used to preserve data, it is also responsible for maintaining the integrity and consistency of the data

I have seen a lot of developers design database, give me the feeling is: in their eyes, the role of the database as its name-just for storing data, in addition to the main key to build, nothing ... There are no check constraints, no indexes, no foreign key constraints, no views, and no stored procedures.

Here, I propose the following database design recommendations:

1. If you want to write code to make sure that the rows in the table are unique, add a primary key to the table.
2. If you want to write code to make sure that a single column in the table is unique, add a constraint to the table.
3. Add a CHECK constraint if you want to write code that determines that the values of columns in a table can only belong to a range.
4. If you want to write code to connect to the parent-child table, create a relationship.
5. If you want to write code to maintain "once a row in the parent table changes, and the related rows in the child table are changed," The cascade deletes and updates are enabled.
6. If you want to invoke a large number of joins to make a query, create a view.
7. If you want to complete a business rule by writing a statement of the database operation, you use a stored procedure.

Note: I do not mention the trigger here, practice proves 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 more headache, so I prefer not to use the trigger at all.

to build a table with a NOT null idea

I found a lot of developers in the table, if you want to create a new field, his idea is this: the Default field is nullable, and then to determine whether it is not to not NULL, if not, OK, this field can be null, and then proceed to the next field. The result is often a table in which all fields except the primary key can be null.

The reason is that the idea is because the null good AH, the program is not easy to make mistakes ah, when you insert the record when you accidentally forget to lose a field, the program can still run, without the "xx field cannot be null" error message.

However, the result is very serious, it will make your program more cumbersome, you have to carry out some meaningless null value processing, to avoid the program error. To make matters worse, if some important data, such as an item value of an order, is null, then you know that any value that operates with null (such as subtraction) results in null, resulting in the result that the total amount of the order is also null.

You can run the following code to try it:

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 you still have to do null processing in the program. Please do not forget that the database also gives you a powerful weapon, which is the Check constraint, when you need to make sure that a field can neither be null nor empty, it can be written like this:

ColumnName    Varchar() not       NullConstraintCheck( Len > 0)

Therefore, the rational way of thinking should be this: the default field is not NULL, and then determine whether the field is not NULL, if not, OK, this field is not null, the next field.

An example script that builds a table

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

Create Tablearticle (IdInt Identity(1,1) not Null, TitleVarchar( -) not Null ConstraintUq_articletitleUnique, KeywordsVarchar( -) not Null, AbstractVarchar( -) not Null, AuthorVarchar( -) not Null Default 'Zhang Ziyang', TypeTinyInt            not Null Default 0 ConstraintCk_articletypeCheck(Typeinch(0,1,2)),--0, original; 1, compilation; 2, translationIsonindexBit                not Null Default 1,--is displayed on the first pageContentText               not Null, SourceCodeVarchar( -)Null,--Download path of program source codeSourceVarchar( -) not Null Default 'tracefact',--Article sourceSrcurlVarchar( Max)Null,--URL of article OriginPostdateDateTime           not Null Default GetDate(), ViewcountInt                not Null Default 0, ClassIdInt                not Null   --foreign key contains fields, article categories    ConstraintPk_articlePrimary Key(Id)--establish a primary key)

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

In addition, I used a unique constraint to ensure the uniqueness of the article title. Since this is my blog post table, there should be no duplicate titles, so you can avoid inserting duplicate values when using INSERT statements. Similar to check constraints, the naming convention here is: Uq_ + table name + field name.

naming the primary key

Follow the default specification for SQL Server (the primary key name that is generated by default when you create a primary key using Enterprise Manager), and the primary key is named Pk_tablename. The primary key is for a table, not for a field, and you can sometimes see a table with a key icon in front of the two fields in the Enterprise Manager (such as the Employeeterritories table of the Northwind sample database that comes with SQL Server 2000). Will mistakenly think that the primary key is for the field, that is, a table has two primary keys, in fact, the wrong, there is only one primary key, but contains two fields, this is often said compound primary key. To have a more vivid understanding, look at the SQL statement that creates the composite primary key, as an example of the many-to-many join table Studentcourse mentioned above:

Alter Table Studentcourse ADD Constraint Primary Key (StudentID, CourseID)

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

naming of foreign keys

The name of the foreign key is the name of the table named Fk_ foreign key, which is referenced by the foreign key. Because the foreign key is the table from which the table is located, the above can be written as fk_ from the table name _ the primary table name .

Foreign keys contain fields that are named, foreign keys that contain fields and foreign keys are completely different concepts. The foreign key contains the name of the field, which is suggested 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 a lot of hotels, so is a one-to-many relationship, cities is the main table (1 square), hotel is from the table (multi-party). In the hotel table, Cityid is used as a foreign key.

We can write this when we implement the foreign key:

Alter Table Hotelinfo ADD Constraint Foreign Key References City (ID)  on Delete  on Update No Action

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

Note: When creating a database table, it is generally necessary to write three SQL script files. The first file contains only all the SQL statements that create the table, that is, the CREATE TABLE statement. The second file contains statements that delete relationships and tables, where all statements that delete a relationship, that is, the drop CONSTRAINT statement in the upper half of the file, all statements that delete the table, and the DROP TABLE statement, focus on the lower half of the file. The third file contains statements that establish relationships between tables. This will make it easier for you to migrate the database, because I don't explain it, and you know it when you try.

For the field that contains the foreign key of the parse table in the many-to-many relationship, we can write this (again back to the many-to-many examples of students ' elective courses):

Establish the foreign key relationship between the parse table Studentcourse and the student table:

Alter Table Studentcourse ADD Constraint Foreign Key References Student (Id)  on Delete  on Update No Action

Establish the foreign key relationship between the parse table Studentcourse and the course table:

Alter Table Studentcourse ADD Constraint Foreign Key References Course (Id)  on Delete  on Update No Action

name of the trigger

consist of three parts:

1. A prefix (TR) that describes the type of database object.
2. The basic section, which describes the tables added by the trigger.
3. Suffixes (_i, _u, _d), showing modification statements (Insert, update, and delete)

name of the stored procedure

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 that you define.

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

Here, there is an interesting place worth pondering. There are two ways that we can name stored procedures according to the rules above:

1. Put the verb in front and the noun behind.
2. Put the noun in front and the verb behind.

I personally recommend using mode 2, now say why:

Take Northwind, for example, if you have 4 stored procedures for the Employees table, named: Premployeeinsert, Premployeeupdate, Premployeedelbyid, Premployeegetbyid

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

At this point, when you look at it with Enterprise Manager, you will find that the stored procedure is arranged in a neat order like this:

Premployeedelbyidpremployeegetbyidpremployeeinsertpremployeeupdateprproductdelbyidprproductgetbyidprproductinsertprproduc Tupdate

It is easy to see that the more time you have stored the process, the more obvious the advantages of this naming method will be.

naming the parameters in a stored procedure

The entry parameters in the stored procedure, I recommend the same as the corresponding field name, here, suppose to write a stored procedure that updates the Northwind database Employees table (simplifies), so to write:

Create ProcedurePremployeeupdatebyid@EmployeeId       Int,    @LastName     NVarchar( -),    @FirstName    NVarchar(Ten) as    UpdateEmployeesSetLastName= @LastName, FirstName= @FirstName    WhereEmployeeId= @EmployeeId    If @ @error <> 0 or @ @RowCount = 0       Raiserror 16001' Update user failed '

Summary

In this article, I first raised the issue of the developer's lack of attention to naming database objects, followed by a brief summary of the names of the data objects.

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

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

I hope this article will bring you help.

Database object naming Reference