Naming rules in Database Design

This blog is followed by the summary blog of the previous blog. The previous blog details several items that need to be summarized and their meanings. This blog summarizes the entire implementation process of the data room charging system, which is divided into three phases: first, system database design; second, system form design; and third, system code writing. I. System Database

This blog is followed by the summary blog of the previous blog. The previous blog details several items that need to be summarized and their meanings. This blog summarizes the entire implementation process of the data room charging system, which is divided into three phases: first, system database design; second, system form design; and third, system code writing. I. System Database

This blog is followed by the summary blog of the previous blog. The previous blog details several items that need to be summarized and their meanings. This blog summarizes the entire implementation process of the data room charging system, which is divided into three phases: first, system database design; second, system form design; and third, system code writing.

I. System Database Design

First of all, summing up an experience. For the first time, it is often painful. If you do the right thing, the result will often change you. Otherwise, the butterfly effect will eventually occur, destroy your future. The same is true for database design. The database of the data room charging system was the first time I designed the database structure myself. It took less than a week to complete the design from the initial conception to the final design, in those seven days, I felt anxious.

1. Initial Design

Before you start designing, you must be familiar with the system. It took less than a week to analyze the overall structure of the system and the functions of each module, and draw the following system structure using the graphic program.

The analysis in the first stage provides great convenience for the later design. With the overall grasp of the system, you can proceed with the next step to start the design.

2. Being designed

During the design, I learned some database design materials and made a step-by-step learning on Database Table naming rules to ensure efficient and convenient query of the expected result set in the table. The naming of the table name, report name, and query name in the database is an important detail, which directly affects the efficiency of database data query in the future. However, it is far from enough to name only each field in the database table. in design, we also need to consider the data type of each field and ensure data integrity.

● Naming conventions for table name, report name, and query name

Coding standards are essential to an excellent programmer. They include the naming of variables, methods, classes, tables, fields, objects, and data types in the database. Only the naming rules of database types are summarized here, laying the foundation for efficient query record writing in the future.

Table 1 data type specification table

Description type	Description	Oracle	SQL Server	Access
Int	Integer	Integer	Int	Number
Number (a, B)	Number with decimal point	Numeric	Numeric	Number
Char (n)	Fixed Length string	Char	Char	Text
Varchar (n)	Variable-length string	Varchar2	Varchar	Text
Date	Date/time	DateTime	Datetime	Date/time
Binary (n)	Binary content	Long raw	Image	OLE Object

Note: In principle, only these six basic types are used. The Bool type is not recommended. Use int instead. When writing program code, the variable type in the program must be higher than the corresponding variable type in the database, to avoid data loss during data type conversion.

Table 2 naming rules for other database objects

Object Name	Prefix	Example
Table)	Tbl _/t _ (or no prefix)	Userinfo/t_user_info/tbl_user_info
View)	V _/v	V_user_info/vuserinfo
Sequence)	Seq _	Seq_user_info
Cluster)	C _	C_user_info
Trigger)	Trg _	Trg_user_info
Stored procedure)	Sp _/p _	Sp_user_info/p_user_info
Function)	F _/fn _	Fn_user_info/f_user_info
Materialized view)	Mv _	Mv_user_info
Package and package body)	Pkg _	Pkg_user_info
Type & type body)	Typ _	Typ_user_info
Primary key)	Pk _	Pk_user_info
Foreign key)	Fk _	Fk_user_info_fieldname
Unique index)	Uk _	Uk_user_info_fieldname
Normal index)	Idx _	Idx_user_info_fieldname
Bitmap index)	Bk _	Bk_user_info_fieldname
Synonym (synonym)	Based on the module/mode of the allocated table
Database link)	No special requirements

3. Summary

Problem:

① Database structure design;

② Object naming;

③ Data type definition;

Solution:

①: Not well done. Most of the Structure Design references the previous database structure;

②: Generally, the table names and field names are neatly named based on various materials, which improves the query efficiency;

③: Generally, the varchar type is mostly used. A few use the bigint type, and the type is also changed later;

How to better:

①: Further analysis of the system should be carried out during design, and the system function diagram and structure flowchart should be drawn;

②: Unified naming principles for objects of the same type;

③: For varchar, which is used to store data, you must use bigint or int to compile the sequence number. You cannot tell which type to use varchar first.