Database Design Guide

Welcome to the CC ++ programming community forum, and interact with 2 million technical staff. If we compare the data of an enterprise to the blood necessary for life, database design is the most important part of the application. The database design materials are full of resources, and university degree courses are also detailed. However, as we have repeatedly stressed, good teachers

Welcome to the C/C ++ programming Community Forum and interact with 2 million technical staff> enter the blood necessary to compare enterprise data to life, the database design is the most important part of the application. The database design materials are full of resources, and university degree courses are also detailed. However, as we have repeatedly stressed, good teachers

Welcome to the C/C ++ programming Community Forum and interact with 2 million technical staff> enter

If we compare the data of an enterprise to the data necessary for life, the database design is the most important part of the application. The database design materials are full of resources, and university degree courses are also detailed. However, as we have repeatedly stressed, even a good teacher is superior to an experienced teacher. So recently I have found some experts who are proficient in Database Design to teach you some database design skills and experience. I have selected 60 of the 60 best skills from the 130 feedbacks I received and compiled these skills into this article. To facilitate indexing, the content is divided into five parts:
Part 2-before Database Design
This section lists 12 basic skills, including naming rules and clarifying business needs.
Part 2: design database tables
A total of 24 guidance skills, covering field design in the table and common issues that should be avoided.
Part 2-selection key
How do I select a key? There are 10 tips specifically for the correct usage of the primary key generated by the system, as well as when and how to index fields for optimal performance.
Part 2-ensuring data integrity
Discuss how to keep the database clear and robust, and minimize harmful data.
Part 2-Tips
Other skills that are not included in the above four sections are varied. With these skills, We hope your database development work will be easier.
--------------------------------------------------------------------------------
Part 2-before Database Design
1. Test the existing environment
When designing a new database, you should not only carefully study the business needs, but also examine the existing system. Most database projects are not built from the beginning; normally, there will always be existing systems in the Organization to meet specific requirements (Automatic Computing may not be implemented ). Obviously, the existing system is not perfect, otherwise you do not have to create a new system. However, the research on the old system allows you to discover some minor problems that may be ignored. In general, it is absolutely good for you to evaluate the existing system.

I once took over a database project developed for a regional transportation company. It is not difficult to work, but Access databases are used. I have set some project design parameters, evaluated these parameters with the customer, and checked the working mode in the development environment in advance. When the application is finally deployed, I saw a few prompts on the terminal and immediately put it in front of me! After several hours, I realized that the company ran two database applications on the network, and the access to the network required explicit and strict user accounts and access permissions. With this understanding, the problem can be solved simply by using the customer's system. The lesson of this project is: Remember, if you develop an application in a public environment such as Access or Interbase, be sure to go deep into the system to figure out what the environment you are facing is.

2. Define standard object naming rules
You must define the naming rules for database objects. For database tables, it is necessary to determine whether the table name is in the plural or singular form from the beginning of the project. In addition, a simple rule is also defined for the table alias (for example, if the table name is a word, the alias takes the first four letters of the word; if the table name is two words, take the first two letters of each word 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 one. The result is a 4-letter alias, and the rest are the same.) for the work table, the table name can be prefixed with WORK _ followed by the name of the application that uses the table. The columns in the table must adopt a complete set of design rules for keys. For example, 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 another example, if your table contains many "money" fields, you may add a _ AMT suffix to each column. Also, we recommend that you use DATE _ as the name for the DATE column.

The naming rules between the checklist name, report name, and query name. You may soon be confused by the names of these different database elements. If you insist on naming the different components of these databases in a unified manner, at least you should use table, query, report, and other prefixes at the beginning of these object names to differentiate them.

If Microsoft Access is used, you can use symbols such as qry, rpt, tbl, and mod to identify objects (for example
Tbl_Employees ). I used tbl to index tables when dealing with SQL Server (or Oracle), but I used sp_company (currently using sp_feft _) to identify the stored procedure, sometimes, if I find a better solution, several copies will be saved. When implementing SQL Server 2000, I use udf _ (or similar flag) to identify the function I have compiled.

[1] [2] [3]