Database Technology-database Naming and Design Specifications

Database Technology-database Naming and Design Specifications database development history has been using a somewhat mysterious system naming database table and field. In the original database management system (DBMS), the results of these naming schemes have become a convention and tradition. However, as database applications become more and more complex, more tables and larger development teams

Database Technology-database Naming and Design Specifications database development history has been using a somewhat mysterious system naming database table and field. In the original database management system (DBMS), the results of these naming schemes have become a convention and tradition. However, as database applications become more and more complex, more tables and larger development teams

Database Technology-database Naming and Design Specifications
In the history of database development, a mysterious system has been used to name database tables and fields. In the original database management system (DBMS), the results of these naming schemes have become a convention and tradition. However, as database applications become more and more complex, more tables and larger development teams become more important for developers to come and go. This makes database objects more powerful, a disciplined naming scheme. A well-defined naming scheme becomes more important when you use the object relational ing (ORM) technology or automatic code generation.

Name table

In most databases, there are three types of tables:

1. Data Tables )-Of course, all tables in a database contain data, but I use this word to refer to actual storage, and we are stimulated to generate databases, start with data, such as customers, order or product table. For example, a customer table contains information about customers such as names, addresses, and telephone fields. The customer is a data table-unlike other table types.

2. Link Tables)-A chain table is used to connect two key fields of two different data tables, forming many one-to-many relationships. For example, you can have many one-to-many relationships between a supplier table and a product table, because each supplier can support multiple products and each product can be sold by multiple suppliers. This requires a third table to link the product to the seller.

3. Select a table (Picklist Tables )-This is a common table that contains a list of fields selected in a data table. For example, you may have a status field in your supplier table. The position of the value to the supplier can be selected from another table. I mean these types of tables as "select" tables, because they allow users to select from the list.

In my naming scheme, I want the table name with each prefix and one of the three prefixes to indicate the table type. I use the following Prefix:

Data Table-The prefix tbl I used. Therefore, remember that my rules are case-insensitive and contain no underscores. You can have the following data tables:

TblCustomer

TblOrder

TblOrderEntry

TblVendor

TblProduct

Chain table-I use a prefix link. Therefore, to contact the product supplier, you will have a table linkVendorProduct.

Select Table-I used the prefix pltbl. Therefore, for the supplier status, you will have a table named pltblVendorStatus. If you have tables of the customer status, you can have pltblCustomerStatus.

Advantages:

I found that this table naming system has several advantages.

1. Obviously, it is easy to tell the data table from the table name.

2. What I have seen (such as Microsoft Access or SQL Server) is that each database application lists your tables alphabetically. By using this prefix scheme, your table will be displayed in alphabetical order when it is of the type.

3. If you develop an Automatic Code Generation Tool of any type, you can easily determine the table names included in the data table by programming. You only need to check the prefix.

Singular/plural

Note that in my data table above, all table names are singular, that is, tblCustomer rather than tblCustomers. Whether you like singular or plural names, you should always use one or more consistent names. I prefer singular, because it seems to be more clean to me.

Other table types include:

Log tables)

Error tables)

System tables)

Each of these can have their own prefix.

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), and 4 tran (Fortran 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. 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 have their own tables respectively ), 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 fields 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.

  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. Likewise, 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 developed Asp, you must know the RecordCount attribute. The naming principle is to use a common name instead of creating your own name. Since Microsoft uses the Count suffix to indicate the number, why not?

  Therefore, 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, then 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, when creating an object in a language (such as C #), sometimes the code generator is used (the Field and attribute name of the object are generated based on the field name of the database ), 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 quickly make the database too complex. More importantly, it is difficult to debug the trigger. If a trigger is accidentally created, this is a headache, so 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 a Null value of an order, is used, then we know that the result of any operation with Null values (such as addition, subtraction, multiplication, division, and so on) 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

     Create a table in the 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 think that the primary key is for the field, that is, a table has two primary keys. In fact, there is only one primary key, but it contains two fields. This is often referred to as 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 named as the name of the table referenced by the foreign key where the foreign key is located. Because the table where the foreign key is located is a slave table, the above formula can be written as fk _ 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. We recommend that you use 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 only contains all the SQL statements used to Create a Table, 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 name of the stored procedure for modifying statements (Insert, Update, and Delete)

        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'

           Reference

           2 database naming principles

           2.1 data files

           If the database uses a file system instead of a bare device, the following naming rules are agreed:

           1) Data Files start with the table space name and end with. dbf. All data files are named with lowercase English letters and numbers. If the tablespace contains multiple data files, add _ after the tablespace name starting from 2nd data files _.

           For example, the system tablespace data files: system. dbf, system_2.dbf

           2) control files of oracle databases are represented by control. ctl. For example, control01.ctl and control02.ctl.

           3) for oracle Database Log Files, use redo for online log files <组名> <文件序列名> . Dbf. The group name and file sequence name are both represented by two digits. For example, the two files in the first group represent redo0101.dbf and redo0102.dbf. Archive logs are represented by arch _ % t _ % s. arc. Both % t and % s are oracle-Defined variables.

           2.2 tablespace

           2.2.1 Database System tablespace

           The database system tablespace includes the system tablespace, temporary tablespace, and rollback segment tablespace. Specify the following naming rules:

           1) The system tablespace is directly limited by the database and cannot be modified.

           2) Temporary tablespace is represented by temp. If there are multiple temporary tablespaces, which start with 2nd temporary tablespaces, are added after temp.

           3) The tablespace In the rollback segment is represented by undotbs. If there are more than one tablespace In the rollback segment, the table space starts from the first tablespace In the rollback segment and is added after undotbs.

           2.2.2 Database User tablespace

           The user tablespace of the database uses ts _ <表空间名> . Table space names are divided:

           1) Data Space: The default tablespace is represented by default. For other tablespaces, they are represented based on the types of tables stored in the tablespaces. For example, the code table is represented by code. The table for storing customer data is represented by customer. Try to use a tablespace to store tables of this type. If a table is very large, you can use a single tablespace.

           2) index space: Add ind _ before the name of the corresponding data table space _. For example, the index space of the user's default tablespace is represented by ts_ind_default. The index tablespace of the code table is represented by ts_ind_code.

           Table 2.3

           The naming rules for database tables are as follows:

           1) The table name starts with T _. The table name cannot exceed 30 characters. The table name contains all words in the singular format, and the words must be capitalized.

           2) Multiple words are connected by underscores. If there are multiple systems in the database, the table name is system name + word or multiple words. The system name is short for the development system, such as VNET.

           3) We recommend that you use the complete words in the table. If the table name contains more than 30 characters, the abbreviation of the word is used forward from the last word. (If there is no contract abbreviation, it is represented by the first four letters of the word ).

           The following rules are used to name fields in a database table:

           1) All database Field Names use lower-case English words, separated. The field length cannot exceed 30 characters.

           2) If the field is code, add _ id after the word.

           3) If this field represents time, _ time is used as the suffix.

           2.4 View

           Database view naming rules are as follows:

           1) The view name starts with V _. The view name cannot exceed 30 characters. View names are expressed in uppercase.

           2) When a view is generated from several tables, it uses an underscore (_) to connect the names of several tables. If there are too many tables, you can simplify the table name, but make sure to list all the table names.

           Sequence 2.5

           Database sequence naming rules are as follows:

           The sequence name starts with seq _, followed by the field name of the sequence. If several fields use the same sequence, use an underscore (_) to join the names of several fields. If different tables use different sequences for the same field names, add the table features after the field names and connect them with underscores. The sequence Name Length cannot exceed 30 characters. The sequence name is represented by lowercase English words.

           2.6 stored procedures

           The naming rules for stored procedures are as follows:

           The stored procedure name starts with Pr _. The length of the stored procedure name cannot exceed 30 characters. The stored procedure name is expressed in lowercase.

           2.7 Functions

           The function naming rules are as follows:

           The function name starts with Fu _. The function name cannot exceed 30 characters. The function name is represented by lowercase English words.

           2.8 trigger

           The trigger naming rules are as follows:

           The trigger name starts with Tr _. The trigger name cannot exceed 30 characters. The trigger name is represented by lowercase English words.

           2.9 primary key

           The primary key naming rules are as follows:

           The primary key name starts with pk _, followed by the table name where the primary key is located. The primary key name cannot exceed 30 characters. If it is too long, you can abbreviated the table name. The abbreviation rule is the same as the abbreviation rule of the table name. The primary key name is represented by lowercase English words.

           2.10 foreign key

           The naming rules for foreign keys are as follows:

           The foreign key name starts with fk _, followed by the table name of the foreign key and the corresponding primary table name (excluding t _). The sub-table name and parent table name are separated by underscores. The length of the foreign key name cannot exceed 30 characters. If it is too long, you can abbreviated the table name. The abbreviation rule is the same as the abbreviation rule of the table name. The foreign key name is represented by lowercase English words.

           2.11 Index

           The index naming rules are as follows:

           1) The index name is represented by lowercase English letters and numbers. The index name cannot exceed 30 characters.

           2) The index corresponding to the primary key has the same name as the primary key.

           3) Each type of index ends.

           4) The unique index starts with uni _, followed by the table name. The general index starts with ind _, followed by the table name.

           5) if the index length is too long, you can abbreviation the table name. The abbreviation rule is the same as the abbreviation rule of the table name.