1. Before designing the database (demand analysis stage)
1) Understand customer needs and ask users how to view future changes in demand. Ask the customer to explain his needs, and as the development continues, ask the customer frequently to ensure that his needs are still in the development purpose.
2) Understanding the business of the enterprise can save a lot of time in the future development stage.
3) Pay attention to input and output.
When defining database table and field requirements (input), you should first check existing or designed reports, queries and views (outputs) to determine which tables and fields are necessary to support these outputs.
For example: If a customer needs a report to sort, segment and sum according to the postal code, you must ensure that it includes a separate postal code field and do not incorporate the postal code into the address field.
4) Create data dictionary and ER charts
ER Charts and data dictionaries can make it clear to anyone who knows a database how to get data from the database. ER graphs are useful for showing relationships between tables, while data dictionaries illustrate the purpose of each field and any alias that may exist. This is completely necessary for the documentization of SQL expressions.
5) Define standard object naming specifications
The naming of various database objects must be standardized.
2. Design of tables and fields (database logic design)
Table design principles
1) Standardization and standardization
Standardization of data helps eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is often considered to have the best balance in performance, scalability, and data integrity. Simply put, the table design principle of databases that comply with the 3NF standard is: "One Fact in One Place" that a table only includes its own basic properties and needs to be decomposed when it is not the properties they have. The relationships between tables are connected by foreign keys. It has the following characteristics: There is a set of tables that specifically store associated data connected by keys.
For example: A 3NF database that stores customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the order-related customers, but it is in the table.
A key value will be stored, which points to the row in the Customer table that contains the customer information.
In fact, it is sometimes necessary not to standardize tables for efficiency reasons.
2) Data Driven
Using data-driven rather than hard-coded methods, many policy changes and maintenance will be much more convenient, greatly enhancing the flexibility and scalability of the system.
For example, if the user interface wants to access external data sources (files, XML documents, other databases, etc.), you might as well store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflows (sending emails, printing letterheads, modifying record status, etc.), the data that generates the workflow can also be stored in the database. Role permission management can also be done through data-driven. In fact, if the process is data-driven, you can put a considerable responsibility on the user, and the user maintains his workflow process.
3) Consider various changes
When designing a database, consider which data fields may change in the future.
For example, this is the case with a surname (note that it is a surname of a Westerner, such as a woman who has her husband’s surname after marriage, etc.). Therefore, when establishing a system to store customer information, the last name field is stored in a separate data table, and fields such as the start date and the end date are also attached, so that the changes in this data entry can be tracked.
Field design principles
4) 3 useful fields that should be added to each table
dRecordCreationDate, default is Now() under VB •, default is GETDATE() under SQL Server.
sRecordCreator, defaults to NOT • NULL DEFAULT USER under SQL Server
nRecordVersion, the record version mark; helps to accurately explain the reasons for the occurrence of null data or data loss in the record
5) Use multiple fields for address and phone
It is not enough to describe a street address with just one line of records. Address_Line1, Address_Line2 and Address_Line3 can provide greater flexibility. Also, it is best to have your own data table, with its own type and tag category.
6) Use role entities to define columns belonging to a certain category
When you need to define things that belong to a specific category or have a specific role, you can use role entities to create specific time relationships, so that you can achieve self-documentation.
For example: Use the PERSON entity and the PERSON_TYPE entity to describe people. For example, when John Smith, Engineer is promoted to John Smith, Director and even finally climb to the high level of John Smith, CIO, and all you have to do is change the key value of the relationship between the two tables PERSON and PERSON_TYPE, and add a date/time field to know when the change occurs. In this way, your PERSON_TYPE table contains all possible types of PERSON, such as Associate, Engineer, Director, CIO or CEO, etc. Another alternative is to change the PERSON record to reflect the changes in the new title, but this will not be able to track the specific time of the person's position in time.
7) Choose numeric types and text types as sufficiently as possible
Be especially careful when using smallint and tinyint types in SQL. For example, if you want to see the total monthly sales, the total amount field type is smallint, then if the total amount exceeds $32,767, the calculation operation cannot be performed.
And text fields of ID type, such as customer ID or order number, should be set larger than generally imagined. Assume that the customer ID is 10 digits long. Then you should set the length of the database table field to 12 or 13 characters long. However, this additional space can be increased in the database without reconstructing the entire database in the future.
8) Add delete marker field
Include a "Delete Tag" field in the table so that the rows can be marked as deleted. Do not delete a row separately in a relational database; it is best to use a data clearing program and carefully maintain the integrity of the index.
3. Select keys and indexes (database logic design)
Key selection principle:
1) Key design 4 principles
Create a foreign key for the associated field.  •
All keys must be unique. •
Avoid using compound keys.  •
Foreign keys are always associated with unique key fields.  •
2) Use the system-generated primary key
When designing a database, the system-generated key is used as the primary key, so the index integrity of the database is actually controlled. In this way, the database and non-human mechanisms effectively control access to each row in the stored data. There is another advantage of using system-generated keys as primary keys: it is easy to find logical flaws when having a consistent key structure.
3) Do not use the user's key (do not allow the primary key to be updated)
When determining which field to use as the table key, be careful about the fields the user will edit. Normally, do not select user-editable fields as keys.
4) Optional keys can sometimes be used as primary keys
Further using optional keys as primary keys can have the ability to establish powerful indexes.
Index usage principles:
Indexing is one of the most efficient ways to get data from a database. 95% of database performance problems can be solved using indexing technology.
1) The logical primary key uses a unique group index, a unique non-group index for system keys (as stored procedures), and a non-group index for any foreign key column. Consider how large the database space is, how tables are accessed, and whether these accesses are mainly used for reading and writing.
2) Most databases index the primary key fields that are automatically created, but don’t forget to index foreign keys. They are also frequently used keys, such as running a query to display a record in the main table and all associated tables.
3) Do not index memo/note fields, do not index large fields (with many characters), as this will make the index take up too much storage space.
4) Do not index commonly used small tables
Don't set any keys for small data tables, let alone if they often have insert and delete operations. Index maintenance for these insert and delete operations may take more time than scanning tablespaces.
4. Data Integrity Design (Database Logical Design)
1) Integrity implementation mechanism:
Entity integrity: primary key
Reference integrity:
Delete data in the parent table: cascade deletion; restricted deletion; empty value
Insert data in the parent table: restricted insertion; recursive insertion
Update data in the parent table: cascading update; restricted update; empty value
DBMS can implement reference integrity in two ways: foreign key implementation mechanism (constraint rules) and trigger implementation mechanism
User-defined integrity:
NOT NULL; CHECK; trigger
2) Force data integrity with constraints rather than business rules
Data integrity is achieved using a database system. This includes not only the integrity achieved through standardization but also the functionality of the data. When writing data, you can also add triggers to ensure the correctness of the data. Don't rely on the business layer to ensure data integrity; it cannot guarantee the integrity of tables (foreign keys) and so cannot be imposed on other integrity rules.
3) Mandatory Instruction of Integrity
Remove harmful data before it enters the database. Indicative integrity features of Activating the database system. This keeps the data clean and forces developers to invest more time in handling error conditions.
4) Use search to control data integrity
The best way to control data integrity is to limit user choices. Whenever possible, users should be provided with a clear list of value for their choice. This will reduce errors and misunderstandings in typing code while providing data consistency. Some public data are particularly suitable for searching: country codes, status codes, etc.
5) Adopt the view
To provide another layer of abstraction between the database and application code, a dedicated view can be created for the application without having to directly access the data tables. Doing so also gives you more freedom when dealing with database changes.
5. Other design skills
1) Avoid using triggers
The function of the trigger can usually be implemented in other ways. The trigger may become a distraction while debugging the program. If you really need to use triggers, you'd better focus on documenting it.
2) Use common English (or any other language) instead of coding
It is best to sort by English name when creating drop-down menus, lists, and reports. If you need to encode, you can attach the English that the user knows next to the encode.
3) Save common information
It is very useful to have a table store general database information specifically. This table stores the current version of the database, the most recent inspection/repair (for Access), the name of the associated design document, the customer and other information. This allows for a simple mechanism to track databases, which is especially useful for non-client/server environments when customers complain that their database is not meeting the desired requirements.
4) Include version mechanism
Introduce a version control mechanism in the database to determine the version of the database in use. Over time, users' needs will always change. Eventually, a modification of the database structure may be required. It is more convenient to store version information directly in the database.
5) Prepare documents
All shortcuts, naming specifications, restrictions and functions must be documented.
Use database tools that annotate tables, columns, triggers, etc. Very useful for development, support, and tracking modifications.
Document the database, or create documents within the database itself or separately. In this way, when you go back to the second version after more than a year, the chances of making mistakes will be greatly reduced.
6) Test, test, repeated test
After establishing or revising the database, the data fields must be tested with the newly entered data by the user. Most importantly, let the user conduct the test and work with the user to ensure that the selected data type meets business requirements. Testing needs to be done before putting the new database into actual service.
7) Check the design
A common technique for checking database design during development is to check databases through the application prototypes it supports. In other words, for each prototype application that ultimately expresses data, you ensure that you check the data model and see how the data is retrieved.
3. Database naming specifications
1. Naming of entities (tables)
1) The table is named after a noun or noun phrase, to determine whether the table name is plural or singular. In addition, define simple rules for the table alias (for example, if the table name is a word, the alias will take the first 4 letters of the word; if the table name is two words, each of the first two letters of the two words form a 4 letter-long alias; if the table name is composed of 3 words, take one of the first two words and then take two letters from the last word, and the result will still form a 4 letter-long alias, and so on)
For work tables, the table name can be prefixed WORK_ attached with the name of the application using the table. During the naming process, just piece together the abbreviation according to the semantics. Note that since ORCLE will unify the field names into one of uppercase or lowercase, it is required to add an underscore.
Example:
Abbreviation of definition Sales: Sal Sales;
Order: Ord Order;
Detail: Dtl Detail;
The sales order details list is named: Sal_Ord_Dtl;
2) If the name of a table or field has only one word, it is recommended not to use the abbreviation, but to use the complete word.
Example:
Abbreviation of definition Material Ma item;
The item table name is: Material, not Ma.
But the field item encoding is: Ma_ID; not Material_ID
3) All stored value list tables are prefixed by Z
The purpose is to sort these value list classes at the end of the database.
4) All redundant classes are named (mainly the accumulated table) and prefixed with X
Redundant classes are fields or tables added when deregulating databases.
5) The associated classes are named by connecting two basic classes with underscores and adding the prefix R, followed by listing the two table names or abbreviations of table names in alphabetical order.
The association table is used to save many-to-many relationships.
If the associated table name is greater than 10 letters, the original table name must be abbreviated. If there is no other reason, it is recommended to use abbreviation.
For example: The table Object has a many-to-many relationship with itself, then the table that holds the many-to-many relationship is named: R_Object;
Table Depart and Employee; there is a many-to-many relationship; the association table is named R_Dept_Emp
2. Naming of attributes (columns)
1) Use meaningful column names, and the columns in the table should adopt a complete set of design rules for keys. Each table will have an automatic ID as the main key, and the logical main key is defined as the first group of candidate key keys. If it is an encoding automatically generated by the database, it will be named as: ID; if it is a custom logical encoding, it will be named with the abbreviation plus "ID". If the key is a numeric type, you can use _NO as the suffix; if it is a character type, you can use _CODE as the suffix. The column names should be marked with standard prefixes and suffixes.
For example: The number field of sales order is named: Sal_Ord_ID; if there is also an automatic number generated by the database, it is named: ID.
2) All attributes are added with suffixes related to the type. Note that if other suffixes are needed, they are placed before the type suffix.
Note: The data type is a field of text, and the type suffix TX can be written without writing. Some fields with more obvious types can be written without writing type suffixes.
3) Named with prefix
If the column names of each table are given a unified prefix, it will be greatly simplified when writing SQL expressions. This does have disadvantages, such as breaking the function of the automatic table connection tool, which links public column names with certain databases.
3. Naming of view
1) The view is prefixed with V, and other naming rules are similar to those of tables;
2) The naming should try to reflect the functions of each view.
4. Naming of triggers
The trigger is prefixed with TR, the trigger name is the corresponding table name plus the suffix, the Insert trigger is plus "_I", the Delete trigger is plus "_D", and the Update trigger is plus "_U", such as: TR_Customer_I, TR_Customer_D, TR_Customer_U.
5. Stored procedure name
The stored procedure should start with "UP_" and be distinguished from the stored procedure of the system. The subsequent parts are mainly composed of verb-object forms, and each component is divided by underscores. For example, the stored procedure for adding an agent's account is "UP_Ins_Agent_Account".
6. Variable name
Variable names are in lowercase. If they belong to the phrase form, separate each word with an underscore, such as @my_err_no.
7. Other precautions in naming
1) The above naming must not exceed the system limit of 30 characters. The length limit for variable names is 29 (excluding the identifier @).
2) The naming of data objects and variables is in English characters, and Chinese naming is prohibited. Never leave spaces between the characters of the object name.
3) Be careful with retained words and make sure that your field name does not conflict with retained words, database system or common access methods.
5) Maintain the consistency of field names and types, and ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, then it should not become a character in another table.
1) Understand customer needs and ask users how to view future changes in demand. Ask the customer to explain his needs, and as the development continues, ask the customer frequently to ensure that his needs are still in the development purpose.
2) Understanding the business of the enterprise can save a lot of time in the future development stage.
3) Pay attention to input and output.
When defining database table and field requirements (input), you should first check existing or designed reports, queries and views (outputs) to determine which tables and fields are necessary to support these outputs.
For example: If a customer needs a report to sort, segment and sum according to the postal code, you must ensure that it includes a separate postal code field and do not incorporate the postal code into the address field.
4) Create data dictionary and ER charts
ER Charts and data dictionaries can make it clear to anyone who knows a database how to get data from the database. ER graphs are useful for showing relationships between tables, while data dictionaries illustrate the purpose of each field and any alias that may exist. This is completely necessary for the documentization of SQL expressions.
5) Define standard object naming specifications
The naming of various database objects must be standardized.
2. Design of tables and fields (database logic design)
Table design principles
1) Standardization and standardization
Standardization of data helps eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is often considered to have the best balance in performance, scalability, and data integrity. Simply put, the table design principle of databases that comply with the 3NF standard is: "One Fact in One Place" that a table only includes its own basic properties and needs to be decomposed when it is not the properties they have. The relationships between tables are connected by foreign keys. It has the following characteristics: There is a set of tables that specifically store associated data connected by keys.
For example: A 3NF database that stores customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the order-related customers, but it is in the table.
A key value will be stored, which points to the row in the Customer table that contains the customer information.
In fact, it is sometimes necessary not to standardize tables for efficiency reasons.
2) Data Driven
Using data-driven rather than hard-coded methods, many policy changes and maintenance will be much more convenient, greatly enhancing the flexibility and scalability of the system.
For example, if the user interface wants to access external data sources (files, XML documents, other databases, etc.), you might as well store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflows (sending emails, printing letterheads, modifying record status, etc.), the data that generates the workflow can also be stored in the database. Role permission management can also be done through data-driven. In fact, if the process is data-driven, you can put a considerable responsibility on the user, and the user maintains his workflow process.
3) Consider various changes
When designing a database, consider which data fields may change in the future.
For example, this is the case with a surname (note that it is a surname of a Westerner, such as a woman who has her husband’s surname after marriage, etc.). Therefore, when establishing a system to store customer information, the last name field is stored in a separate data table, and fields such as the start date and the end date are also attached, so that the changes in this data entry can be tracked.
Field design principles
4) 3 useful fields that should be added to each table
dRecordCreationDate, default is Now() under VB •, default is GETDATE() under SQL Server.
sRecordCreator, defaults to NOT • NULL DEFAULT USER under SQL Server
nRecordVersion, the record version mark; helps to accurately explain the reasons for the occurrence of null data or data loss in the record
5) Use multiple fields for address and phone
It is not enough to describe a street address with just one line of records. Address_Line1, Address_Line2 and Address_Line3 can provide greater flexibility. Also, it is best to have your own data table, with its own type and tag category.
6) Use role entities to define columns belonging to a certain category
When you need to define things that belong to a specific category or have a specific role, you can use role entities to create specific time relationships, so that you can achieve self-documentation.
For example: Use the PERSON entity and the PERSON_TYPE entity to describe people. For example, when John Smith, Engineer is promoted to John Smith, Director and even finally climb to the high level of John Smith, CIO, and all you have to do is change the key value of the relationship between the two tables PERSON and PERSON_TYPE, and add a date/time field to know when the change occurs. In this way, your PERSON_TYPE table contains all possible types of PERSON, such as Associate, Engineer, Director, CIO or CEO, etc. Another alternative is to change the PERSON record to reflect the changes in the new title, but this will not be able to track the specific time of the person's position in time.
7) Choose numeric types and text types as sufficiently as possible
Be especially careful when using smallint and tinyint types in SQL. For example, if you want to see the total monthly sales, the total amount field type is smallint, then if the total amount exceeds $32,767, the calculation operation cannot be performed.
And text fields of ID type, such as customer ID or order number, should be set larger than generally imagined. Assume that the customer ID is 10 digits long. Then you should set the length of the database table field to 12 or 13 characters long. However, this additional space can be increased in the database without reconstructing the entire database in the future.
8) Add delete marker field
Include a "Delete Tag" field in the table so that the rows can be marked as deleted. Do not delete a row separately in a relational database; it is best to use a data clearing program and carefully maintain the integrity of the index.
3. Select keys and indexes (database logic design)
Key selection principle:
1) Key design 4 principles
Create a foreign key for the associated field.  •
All keys must be unique. •
Avoid using compound keys.  •
Foreign keys are always associated with unique key fields.  •
2) Use the system-generated primary key
When designing a database, the system-generated key is used as the primary key, so the index integrity of the database is actually controlled. In this way, the database and non-human mechanisms effectively control access to each row in the stored data. There is another advantage of using system-generated keys as primary keys: it is easy to find logical flaws when having a consistent key structure.
3) Do not use the user's key (do not allow the primary key to be updated)
When determining which field to use as the table key, be careful about the fields the user will edit. Normally, do not select user-editable fields as keys.
4) Optional keys can sometimes be used as primary keys
Further using optional keys as primary keys can have the ability to establish powerful indexes.
Index usage principles:
Indexing is one of the most efficient ways to get data from a database. 95% of database performance problems can be solved using indexing technology.
1) The logical primary key uses a unique group index, a unique non-group index for system keys (as stored procedures), and a non-group index for any foreign key column. Consider how large the database space is, how tables are accessed, and whether these accesses are mainly used for reading and writing.
2) Most databases index the primary key fields that are automatically created, but don’t forget to index foreign keys. They are also frequently used keys, such as running a query to display a record in the main table and all associated tables.
3) Do not index memo/note fields, do not index large fields (with many characters), as this will make the index take up too much storage space.
4) Do not index commonly used small tables
Don't set any keys for small data tables, let alone if they often have insert and delete operations. Index maintenance for these insert and delete operations may take more time than scanning tablespaces.
4. Data Integrity Design (Database Logical Design)
1) Integrity implementation mechanism:
Entity integrity: primary key
Reference integrity:
Delete data in the parent table: cascade deletion; restricted deletion; empty value
Insert data in the parent table: restricted insertion; recursive insertion
Update data in the parent table: cascading update; restricted update; empty value
DBMS can implement reference integrity in two ways: foreign key implementation mechanism (constraint rules) and trigger implementation mechanism
User-defined integrity:
NOT NULL; CHECK; trigger
2) Force data integrity with constraints rather than business rules
Data integrity is achieved using a database system. This includes not only the integrity achieved through standardization but also the functionality of the data. When writing data, you can also add triggers to ensure the correctness of the data. Don't rely on the business layer to ensure data integrity; it cannot guarantee the integrity of tables (foreign keys) and so cannot be imposed on other integrity rules.
3) Mandatory Instruction of Integrity
Remove harmful data before it enters the database. Indicative integrity features of Activating the database system. This keeps the data clean and forces developers to invest more time in handling error conditions.
4) Use search to control data integrity
The best way to control data integrity is to limit user choices. Whenever possible, users should be provided with a clear list of value for their choice. This will reduce errors and misunderstandings in typing code while providing data consistency. Some public data are particularly suitable for searching: country codes, status codes, etc.
5) Adopt the view
To provide another layer of abstraction between the database and application code, a dedicated view can be created for the application without having to directly access the data tables. Doing so also gives you more freedom when dealing with database changes.
5. Other design skills
1) Avoid using triggers
The function of the trigger can usually be implemented in other ways. The trigger may become a distraction while debugging the program. If you really need to use triggers, you'd better focus on documenting it.
2) Use common English (or any other language) instead of coding
It is best to sort by English name when creating drop-down menus, lists, and reports. If you need to encode, you can attach the English that the user knows next to the encode.
3) Save common information
It is very useful to have a table store general database information specifically. This table stores the current version of the database, the most recent inspection/repair (for Access), the name of the associated design document, the customer and other information. This allows for a simple mechanism to track databases, which is especially useful for non-client/server environments when customers complain that their database is not meeting the desired requirements.
4) Include version mechanism
Introduce a version control mechanism in the database to determine the version of the database in use. Over time, users' needs will always change. Eventually, a modification of the database structure may be required. It is more convenient to store version information directly in the database.
5) Prepare documents
All shortcuts, naming specifications, restrictions and functions must be documented.
Use database tools that annotate tables, columns, triggers, etc. Very useful for development, support, and tracking modifications.
Document the database, or create documents within the database itself or separately. In this way, when you go back to the second version after more than a year, the chances of making mistakes will be greatly reduced.
6) Test, test, repeated test
After establishing or revising the database, the data fields must be tested with the newly entered data by the user. Most importantly, let the user conduct the test and work with the user to ensure that the selected data type meets business requirements. Testing needs to be done before putting the new database into actual service.
7) Check the design
A common technique for checking database design during development is to check databases through the application prototypes it supports. In other words, for each prototype application that ultimately expresses data, you ensure that you check the data model and see how the data is retrieved.
3. Database naming specifications
1. Naming of entities (tables)
1) The table is named after a noun or noun phrase, to determine whether the table name is plural or singular. In addition, define simple rules for the table alias (for example, if the table name is a word, the alias will take the first 4 letters of the word; if the table name is two words, each of the first two letters of the two words form a 4 letter-long alias; if the table name is composed of 3 words, take one of the first two words and then take two letters from the last word, and the result will still form a 4 letter-long alias, and so on)
For work tables, the table name can be prefixed WORK_ attached with the name of the application using the table. During the naming process, just piece together the abbreviation according to the semantics. Note that since ORCLE will unify the field names into one of uppercase or lowercase, it is required to add an underscore.
Example:
Abbreviation of definition Sales: Sal Sales;
Order: Ord Order;
Detail: Dtl Detail;
The sales order details list is named: Sal_Ord_Dtl;
2) If the name of a table or field has only one word, it is recommended not to use the abbreviation, but to use the complete word.
Example:
Abbreviation of definition Material Ma item;
The item table name is: Material, not Ma.
But the field item encoding is: Ma_ID; not Material_ID
3) All stored value list tables are prefixed by Z
The purpose is to sort these value list classes at the end of the database.
4) All redundant classes are named (mainly the accumulated table) and prefixed with X
Redundant classes are fields or tables added when deregulating databases.
5) The associated classes are named by connecting two basic classes with underscores and adding the prefix R, followed by listing the two table names or abbreviations of table names in alphabetical order.
The association table is used to save many-to-many relationships.
If the associated table name is greater than 10 letters, the original table name must be abbreviated. If there is no other reason, it is recommended to use abbreviation.
For example: The table Object has a many-to-many relationship with itself, then the table that holds the many-to-many relationship is named: R_Object;
Table Depart and Employee; there is a many-to-many relationship; the association table is named R_Dept_Emp
2. Naming of attributes (columns)
1) Use meaningful column names, and the columns in the table should adopt a complete set of design rules for keys. Each table will have an automatic ID as the main key, and the logical main key is defined as the first group of candidate key keys. If it is an encoding automatically generated by the database, it will be named as: ID; if it is a custom logical encoding, it will be named with the abbreviation plus "ID". If the key is a numeric type, you can use _NO as the suffix; if it is a character type, you can use _CODE as the suffix. The column names should be marked with standard prefixes and suffixes.
For example: The number field of sales order is named: Sal_Ord_ID; if there is also an automatic number generated by the database, it is named: ID.
2) All attributes are added with suffixes related to the type. Note that if other suffixes are needed, they are placed before the type suffix.
Note: The data type is a field of text, and the type suffix TX can be written without writing. Some fields with more obvious types can be written without writing type suffixes.
3) Named with prefix
If the column names of each table are given a unified prefix, it will be greatly simplified when writing SQL expressions. This does have disadvantages, such as breaking the function of the automatic table connection tool, which links public column names with certain databases.
3. Naming of view
1) The view is prefixed with V, and other naming rules are similar to those of tables;
2) The naming should try to reflect the functions of each view.
4. Naming of triggers
The trigger is prefixed with TR, the trigger name is the corresponding table name plus the suffix, the Insert trigger is plus "_I", the Delete trigger is plus "_D", and the Update trigger is plus "_U", such as: TR_Customer_I, TR_Customer_D, TR_Customer_U.
5. Stored procedure name
The stored procedure should start with "UP_" and be distinguished from the stored procedure of the system. The subsequent parts are mainly composed of verb-object forms, and each component is divided by underscores. For example, the stored procedure for adding an agent's account is "UP_Ins_Agent_Account".
6. Variable name
Variable names are in lowercase. If they belong to the phrase form, separate each word with an underscore, such as @my_err_no.
7. Other precautions in naming
1) The above naming must not exceed the system limit of 30 characters. The length limit for variable names is 29 (excluding the identifier @).
2) The naming of data objects and variables is in English characters, and Chinese naming is prohibited. Never leave spaces between the characters of the object name.
3) Be careful with retained words and make sure that your field name does not conflict with retained words, database system or common access methods.
5) Maintain the consistency of field names and types, and ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, then it should not become a character in another table.