Database design skills are provided

1. Before designing the database (demand analysis stage)
1) understand customer requirements and ask users how to view future demand changes. Ask the customer to explain their needs, and as the development continues, ask the customer frequently to ensure that their needs are still under development.
2) understanding enterprise business can save a lot of time in the future development phase.
3) Pay attention to input and output.
When defining database tables and field requirements (input), Check Existing or designed reports, queries, and views (output) to determine which tables and fields are necessary to support these outputs.
For example, if you want a report to be sorted, segmented, and summed by zip code, make sure that it contains a separate zip code field instead of mixing the zip code into the address field.
4) create a data dictionary and ER chart
ER charts and data dictionaries allow anyone who knows the database to clearly learn how to obtain data from the database. The erdiagram is useful for displaying the relationship between tables, while the data dictionary describes the purpose of each field and any possible aliases. It is absolutely necessary to document SQL expressions.
5) define standard object naming rules
The names of various objects in the database must be standardized.
2. Table and field design (Database Logic Design)
Table Design Principles
1) standardization and standardization
Data Standardization helps eliminate data redundancy in databases. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to have achieved the best balance in terms of performance, scalability and data integrity. To put it simply, the table design principle for databases that comply with the 3NF standard is: "One Fact in One Place", that is, a table only includes its basic attributes, when they are not their own properties, they need to be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables specifically store the associated data connected by keys.
For example, a 3NF database storing customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the customer associated with the Order,
A key value is stored, pointing to the line containing the Customer information in the Customer table.
In fact, for the sake of efficiency, it is sometimes necessary not to standardize the table.
2) data-driven
Using data-driven, not hard-coded methods, many policy changes and maintenance are much easier, greatly enhancing system flexibility and scalability.
For example, if you want to access external data sources (files, XML documents, and other databases) on the user interface, you may wish to store the connection and path information in the user interface support table. In addition, if you execute tasks such as workflows on the user interface (such as sending emails, printing letterhead, and modifying record status), the workflow data can also be stored in the database. Role permission management can also be completed through data-driven. In fact, if the process is data-driven, you can push a considerable amount of responsibility to users to maintain their own workflow processes.
3) consider various changes
When designing a database, consider which data fields may change in the future.
For example, the last name is the same as the last name of a Western person, for example, the last name of a female after marriage ). Therefore, when a system is created to store customer information, the last name field is stored in a separate data table, and fields such as the start date and end date are appended, in this way, we can track the changes of this data entry.
Field Design Principles
4) three useful fields should be added to each table
DRecordCreationDate, which is Now () by default in VB sequence •, and GETDATE () by default in SQL Server ()
SRecordCreator, which defaults to NOT exist • null default user in SQL Server
NRecordVersion indicates the record version. It helps to accurately describe the causes of null Records • data loss or
5) use multiple fields for the address and phone number
It is not enough to describe the street address in just one line. Address_Line1, Address_Line2, and Address_Line3 provide greater flexibility. In addition, it is recommended that you have your own data table with your phone number and email address.
6) use the role entity to define columns of a certain type
When defining things belonging to a specific category or with a specific role, you can use the role entity to create a specific time-related relationship, so as to achieve self-documenting.
For example, the PERSON object and the PERSON_TYPE object are used to describe the PERSON. For example, when John Smith and Engineer are promoted to John Smith, Director and finally to John Smith, the CIO is high, all you need to do is change the key value of the relationship between PERSON and PERSON_TYPE in two tables, and add a date/time field to know when the change will happen. In this way, your PERSON_TYPE table contains the possible types of all persons, such as Associate, Engineer, ctor, CIO, or CEO. Another alternative is to change the PERSON record to reflect the change of the new title, but in this way, the specific time of the individual's position cannot be tracked in time.
7) Select numeric and text types as adequate as possible
Be careful when using smallint and tinyint types in SQL. For example, if you want to see the total monthly sales amount and the Field Type of the total amount is smallint, you cannot perform the calculation if the total amount exceeds $32,767.
Text fields of the ID type, such as the customer ID or order number, should be set to be larger than expected. Assume that the customer ID is 10 digits long. Then you should set the field length of the database table to 12 or 13 characters. However, this extra space can increase the database size without restructuring the entire database in the future.
8) add and delete tag Fields
The table contains a "delete tag" field to mark rows as deleted. Do not delete a single row in a relational database. It is best to clear the Data Program and carefully maintain the integrity of the index.
3. Select keys and indexes (Database Logic Design)
Key selection principles:
1) Key design 4 Principles
Create a foreign key for the associated field. Elastic •
All keys must be unique. Elastic •
Avoid using compound keys. Elastic •
Foreign keys are always associated with unique key fields. Elastic •
2) use the primary key generated by the system
When designing a database, the System-generated key is used as the primary key, which actually controls the index integrity of the database. In this way, the database and non-manual mechanism effectively control access to each row of stored data. Using the system-generated key as the primary key has another advantage: when having a consistent key structure, it is easy to find logical defects.
3) do not use the user's key (do not make the primary key updatable)
When determining the fields used as the table key, you must be careful with the fields to be edited. Generally, do not select an Editable field as the key.
4) Optional keys can sometimes be used as primary keys.
The optional keys can be further used as primary keys, which can be used to build powerful indexes.
Indexing principles:
Indexing is one of the most efficient ways to obtain data from a database. Index technology can be used to solve 95% of database performance problems.
1) the logical primary key uses a unique group index, and uses a unique non-group index for the system key (as a stored procedure) and a non-group index for any foreign key column. Consider the size of the database space, how tables are accessed, and whether these accesses are mainly used for reading and writing.
2) most databases index primary key fields automatically created, but do not forget to index Foreign keys, which are also frequently used keys, for example, running a query to display a record of the master table and all associated tables can be used.
3) do not index memo/note fields, or index large fields (with many characters). This will occupy too much storage space for indexes.
4) do not index frequently used small tables
Do not set any keys for small data tables. Do not do this if they are often inserted or deleted. The index maintenance for these insert and delete operations may consume more time than the scan tablespace.
4. Data Integrity Design (Database Logic Design)
1) Integrity implementation mechanism:
Entity Integrity: Primary Key
Integrity of reference:
Delete data in the parent table: cascade Delete; restricted Delete; null
Insert data in the parent table: limited insert; recursive insert
Update Data in the parent table: cascade update; restricted update; Null Value
DBMS provides two methods to achieve the integrity of the Reference: The foreign key implementation mechanism (constraint rules) and the trigger implementation mechanism.
User-Defined integrity:
Not null; CHECK; trigger
2) Use constraints rather than business rules to force data integrity
The database system is used for data integrity. This includes not only integrity achieved through standardization, but also data functionality. When writing data, you can also add triggers to ensure data correctness. Do not rely on the business layer to ensure data integrity; it cannot guarantee the integrity between tables (Foreign keys), so it cannot be imposed on other integrity rules.
3) mandatory indication integrity
Remove harmful data before it enters the database. Activate the indication integrity feature of the database system. In this way, data can be kept clean and developers can be forced to spend more time processing error conditions.
4) use search to control data integrity
The best way to control data integrity is to restrict user selection. Whenever possible, users should be provided with a clear value list for their choice. This reduces the errors and misunderstandings of the entered code and provides data consistency. Some public data is particularly suitable for searching: Country Code, Status Code, etc.
5) view
To provide another abstraction layer between the database and application code, you can create a special view for the application without requiring the application to directly access the data table. This gives you more freedom to handle database changes.
5. other design skills
1) Avoid using triggers
The functions of a trigger can be implemented in other ways. When debugging a program, the trigger may become interference. If you do need a trigger, you 'd better document it in a centralized manner.
2) use common English (or any other language) instead of coding
When creating drop-down menus, lists, and reports, it is best to sort them by English name. If encoding is required, you can attach the English language that the user knows to the encoding.
3) save common information
It is very useful to allow a table to store general database information. Store the current database version, recent check/repair (Access), name of the associated design document, and customer information in this table. In this way, a simple mechanism can be implemented to track databases. When customers complain that their databases do not meet the expected requirements and contact you, this is especially useful for non-client/server environments.
4) include version Mechanism
Introduce the version control mechanism in the database to determine the version of the database in use. After a long period of time, users' needs will always change. In the end, you may need to modify the database structure. It is more convenient to store version information directly in the database.
5) document preparation
All shortcuts, naming conventions, restrictions, and functions must be documented.
Database tools that annotate tables, columns, and triggers are used. It is useful for development, support, and tracking and modification.
Document the database, or create documents in the database itself or separately. In this way, after more than a year, I will go back to the 2nd versions, and the chances of making mistakes will be greatly reduced.
6) tests, tests, and repeated tests
After creating or revising the database, you must use the new data test data field entered by the user. The most important thing is to allow users to perform tests and ensure that the selected data type meets the commercial requirements. The test should be completed before the new database is put into the actual service.
7) Check Design
The common technology used to check the database design during development is to check the database through its supported application prototype. In other words, for each prototype application that ultimately expresses data, you must check the data model and view how to retrieve the data.
Iii. Database naming rules
1. Object (table) Naming
1) The table is named by a noun or a noun phrase to determine whether the table name is in the form of a plural or singular. In addition, a simple rule is defined for the table alias (for example, if the table name is a word, an alias is the first four letters of a word. If the table name is two words, the first two letters of each word are used to form an alias with four letters; if the table name is composed of three words, take one of the first two words, and then extract two letters from the last word, the result is a 4-letter alias, and the rest are in turn)
For a working table, the table name can be prefixed with WORK _ followed by the name of the application using the table. During the naming process, you can piece together the abbreviations Based on the semantics. Note that the ORCLE field names must be in uppercase or lowercase letters, so the field names must be underlined.
Example:
Sales: Sal Sales;
Order: Ord Order;
Detail: Dtl details;
The sales order list is named Sal_Ord_Dtl;
2) If the table or field name has only one word, we recommend that you use a complete word instead of an abbreviation.
Example:
The abbreviation of the definition is Material Ma;
The item table name is Material, not Ma.
However, the field item code is Ma_ID, not Material_ID.
3) Add the prefix Z to the table of all stored values.
The purpose is to sort these Value List classes at the end of the database.
4) Add the prefix X to the names of all redundant classes (mainly accumulative tables ).
The redundancy class is used to improve database efficiency. It is used to add fields or tables to a non-standardized database.
5) join classes connect two basic classes with underscores and add the prefix R to name them. The names of the two tables or abbreviations of the table names are listed in alphabetical order.
An 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 are no other reasons, we recommend that you use abbreviations.
For example, if a table Object has many-to-many relationships with itself, the name of the table that stores many-to-many relationships is R_Object;
The table Depart and Employee have many-to-many relationships. The associated table is named R_Dept_Emp.
2. Name of attributes (columns)
1) Use meaningful column names. The columns in the table must adopt a complete set of design rules for keys. Each table will have an automatic ID as the primary key, and the logical primary key is defined as the first group of Candidate Primary keys. If the encoding is automatically generated by the database, it is uniformly named: ID; for custom logic encoding, use the abbreviation "ID. If the key is of the numeric type, you can use _ NO as the suffix. If the key is of the character type, you can use the _ CODE suffix. Standard prefixes and suffixes should be used for column names.
For example, name Sal_Ord_ID as the number field of a sales order. If there is an automatic number generated by a database, name it: ID.
2) Add the type suffix to all attributes. Note that if other suffixes are required, they are placed before the type suffix.
Note: The data type is a text field, and the type suffix TX can be left empty. Some fields with obvious types can be left with a type suffix.
3) prefix naming
Use a uniform prefix for the column names of each table, which greatly simplifies the preparation of SQL expressions. This operation also has some disadvantages. For example, the function of the automatic table Connection Tool is damaged, and the latter associates the public column names with some databases.
3. View naming
1) The view is prefixed with V. Other naming rules are similar to the table naming rules;
2) Naming should reflect the functions of each view as much as possible.
4. Trigger name
The trigger uses TR as the prefix, and the trigger name is suffixed with the corresponding table name. The Insert trigger adds "_ I", the Delete trigger adds "_ D", and the Update trigger adds "_ U ", for example, TR_Customer_ I, TR_Customer_D, and TR_Customer_U.
5. Stored Procedure name
The stored procedure should start with "UP _" and be differentiated from the stored procedure of the system. The subsequent parts are mainly composed of dynamic objects and separated by underscores. For example, the stored procedure of adding an agent account is "UP_Ins_Agent_Account ".
6. variable name
The variable name is in lowercase. If it is in the phrase format, each word is separated by an underscore, for example, @ my_err_no.
7. Other considerations
1) The names must not exceed 30 characters. The variable name length is limited to 29 (excluding the identifier character @).
2) The names of Data Objects and variables are all English characters. Chinese names are prohibited. Do not leave spaces between characters in the object name.
3) Be careful when retaining words. Ensure that your field names do not conflict with the reserved words, database systems, or common access methods.
5) ensure the consistency of Field Names and types. Ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, the Data Type in the other table should not be converted to the numeric type.