If the business's data is more necessary than life, the design is the most important part of the application. There are also specialized testers in the materials and sweat recharge, and university degree courses. However, as we have repeatedly emphasized, good teachers are more than experienced teachings. So we have recently found some professionals who have acclaimed database design to teach you some design database skills and experience. Our editing seizes 60 best techniques from 130 feedbacks, and prepares these techniques into this article. In order to facilitate indexing of its contents into 5 parts:
Part 1 - Before designing the database
This part is listed 12 basic skills, including naming specifications and clear business needs.
Part 2 - Design Database Table
24 guidelines, covering the field design and common problems that should be avoided.
Part 3 - Select button
How to choose the key? Here are 10 techniques to specifically involve the correct usage of the primary key generated by the system, and when and how to index fields to achieve optimal performance.
Part 4 - Guarantee data integrity
Discuss how to keep the database clear and robust, how to reduce harmful data to a minimum.
Part 5 - Various tips
Do not include other techniques in the four parts, the five-flowers, with them hope that your database development work is more easily.
Part 1 - Before designing the database
1. Examination of existing environments
When designing a new database, you should not only study business needs carefully and to examine existing systems. Most database projects are not established from the beginning; in general, there is always an existing system used to meet specific needs (possibly automatic calculations). Obviously, existing systems are not perfect, otherwise you don't have to build a new system. But research on the old system allows you to find some subtle problems that may ignore. In general, examining existing systems is absolutely beneficial to you. - Lamont Adams
I have taken over a database project developed by regional transportation companies, it is not difficult to use the Access database. I set some project design parameters, and the customer was evaluated with the customer. In advance, the working mode taken in advance. When the final deployment is applied, I only see a few prompts on the terminal and then Rapida is in front of me! I took a toss for my ear for hours, I realized that the two database applications were ran on the network of this company, and access to the network requires clear and strict user accounts and their access. Understand this, the problem is solved: Just use the customer's system. This project gives me the lesson is: Remember, if you develop applications in public environments such as Access or Interbase, be sure to go deep into the system inside the system. What is your facing environment? - kg
2. Define the standard object naming specification
Be sure to define the naming specification of the database object. For database tables, you have to determine the table name from the beginning of the project to use the plural or singular form. In addition, the alias for the table is a simple rule (for example, if the table name is a word, the alias will take the first 4 letters of the word; if the name is two words, the first two letters of the two words Composition 4 letters long alias; if the name of the table consists of 3 words, you may wish to take one of the first two words and then remove the two letters from the last word, and the result is a different alias, the rest. According to a secondary push), the table name can be added to the prefix Work_ to adopt the name of the application using the table. The columns in the table should use a set of design rules for the keys. For example, if the key is a digital type, you can use _no as a suffix; if it is a character type, you can use the _code suffix. The column name should adopt a standard prefix and suffix. For another example, if there is a lot of "Money" fields in your table, you may wish to add a _AMT suffix for each column. Also, the date column is best taking DATE_ as a name. - Richard checks the naming specification between the table name, the report name, and the 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, at least you should distinguish between these object names with Table, Query or Report et al. - rrydenm
If Microsoft Access is used, you can identify objects (such as TBL_EMPLOYEES) with QRY, RPT, TBL, and MOD. I used TBL to independing on the table when I was deal with SQL Server (or Oracle), but I used sp_company (now using sp_feft_) to identify stored procedures, because in some time I found a better handling method often saved Several copies. I identify the function I have written with UDF_ (or similar tags) when implementing SQL Server 2000. - TIMOTHY J. Bruce
3. Pre-plan
In the early 1980s, I was still using the asset account system and the System 38 platform. At that time, I was responsible for designing all the date fields, so that I can easily handle the 2000 problem in the future. Many people say that I don't want to solve this problem because I have to handle it too much trouble (this is a long time before the Y2K problem in the world). I will say that I will not encounter a big happening as long as I plan to plan. As a result, I only finished the procedure for two weeks. Because of the pre-plan, the Y2K problem has fallen to the lowest level (recently heard that the program is even running on the AS / 400 system in 1995, the unique small problem is from the code to remove the annotation fee from the code Point workfinder). - generalist
4. Get data mode resource manual
People who are seeking sample modes can read the book by Len Silverston, W. H.inmon and Kent Graziano, is a book worth having the best data modeling book. The book covers a variety of data fields, such as personnel, institutions, and work performance. - Minstelmike
5. Implementing the future, but not forgetting the lessons of the past
I have found that the user see how the future demand changes is very useful. This can achieve two purposes: First, you can clearly understand which place should be more flexible and how to avoid performance bottlenecks; secondly, you know that users will be as surprised as you when there is no certain demand change in advance. . CHRISDK
Be sure to remember the lessons of the past! Our developers should also help each other by sharing their experiences and experience. Even if the user thinks that they don't need any support, we should also conduct this education in this area, we have been facing this moment "I have to do this." - DHATTREM6. Logic design before physical practice
The logic design is first taken before deeply in-depth physical design. As a large number of CASE tools continue to emerge, your design can also achieve considerable logic level, you can usually better understand the aspects of the database designs. - Chardove
7. Learn about your business
In your 100% confirming the system from the customer's angle to meet the needs of its needs, don't add even a data sheet (why, you have no mode yet? Please refer to the skills 9). Understanding your business business can save a lot of time in the future development phase. Once you have identified your business needs, you can make many decisions yourself. - Rangel
Once you think you have clarified your business content, you'd better communicate with our customers. Adopt customer terms and explain to them what you think and you are heard. At the same time, it should also be used, and the system will be used to express the system's relational base. This way you can make your customers correct your own understanding and do the next ER design. - TEBURLEW
8. Create a data dictionary and ER chart
Be sure to take some time to create an ER chart and data dictionary. At least at least the data type of each field and the primary keys within each table. It is entirely necessary when creating the ER chart and the data dictionary but it is entirely necessary to understand other developers. The sooner creation can help avoid the possibility of being confusing in the future, so that anyone who knows the database can make data from the database. - BGUMBERT
There is a very importance of the latest document, such as ER chart, is emphasized, which is useful to indicate the relationship between the table, and the data dictionary illustrates the use of each field and any alias that may exist. This is exact essential for documentation for SQL expressions. - vanduin.chris.cj
9. Creating a mode
A chart wins thousands of words: developers must not only read and implement it, but also use it to help themselves and user conversations. The pattern helps improve collaboration efficiency, so it is almost impossible to have a big problem in the initial database design. The pattern doesn't have to be very complicated; it can even be simple to write on a piece of paper. It is only necessary to ensure that the logical relationship on it can produce benefits in the future. - Dana Daigle
10. From the input and output
When defining database tables and field requirements (input), you should first check existing or designed reports, queries, and views (outputs) to determine which of these outputs are necessary and fields. For a simple example: If the customer needs a report to sort by postal coding, segmentation and summing, you have to ensure that a separate postal coding field is included without using the postal code into the address field. - Peter.Marshall
11. Report Tips
To understand how users report data: batch or submit reports online? The time interval is daily, weekly, monthly, every quarter or every year? You can also consider creating a summary table if needed. The primary key generated by the system is difficult to manage in the report. The user is retrieved with a sub-key within a table with a system generated primary key, often returns a number of repetitive data. Such retrieval performance is relatively low and it is easy to cause confusion. - KOL
12. Understand customer needs
It seems that this should be obvious, but the demand is from the customer (here you want to consider from internal and external customers). Don't rely on the needs written down, real needs in your head. You have to let customers explain their needs, and as the development continues, it is necessary to ask customers to ensure that their demand is still developed. A unchanging truth is: "Only I saw me know what I want" will cause a lot of rework, because the database does not meet the needs standards that customers have never written. What is even worse is that your explanation of them is only yourself, and it may be completely wrong. - kgilson
Part 2 - Design Tables and Fields
1. Check a variety of changes
I will take into account what data fields will change in the future when designing the database. For example, the surname is this (note is the surname of Westerners, such as women who have married after marriage). So, when establishing a system storage customer information, I tend to store the last name field in a separate data table, but also attach the starting day and terminating day, so that this data entry can be tracked. - Shropshire Lad
2. Use meaningful field name
One time I participated in the development of a project, there were programs that inherited from other programmers. The programmer likes to display the data indicating the use of the data in the screen, which is not good, but unfortunately, she still likes to use some strange Name method, its naming adopts the combination of Hungarian naming and control serial number, such as CBO1, TXT2, TXT2_B, and the like. Unless you are using a system that is only to your abbreviated field name, please clearly describe the fields as well as possible. Of course, don't do your head, such as Customer_Shipping_Address_Street_Line_1 I, although it is clear, no one is willing to type such a long name, the specific scale is in your grasp. - Lamont Adams
3. Name the prefix
If there are many fields in multiple tables (such as firstname), you may wish to help you identify fields with a particular table prefix (such as cuslastname). - NotoriousDog
Time data should include the "Recent Update Date / Time" field. Time markers For the reasons for finding data issues, rescreasting / overloading data by date and clears the old data is particularly useful. - KOL
5. Standardization and data driver
The standardization of data is not only convenient and convenient for others. For example, if your user interface is to access external data sources (files, XML documents, other databases, etc.), you might as well as store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (send mail, print letter, modifying record status, etc.), then data generating workflow can also be stored in the database. The pre-arrangement will always work hard, but if these processes use data drivers rather than hard coding, then strategy changes and maintenance will be much easier. In fact, if the process is data-driven, you can push considerable responsibility to the user, by the user to maintain its workflow process. - TDUVALL
6. Standardization cannot be over
For those who are not familiar with standardization (Normalization), standardization can ensure that the fields are all basic elements, and this measure will help eliminate data redundancy in the database. Standardization has several forms, but Third Normal Form (3NF) is often considered to have the best balance in performance, scalability, and data integrity. Simply, 3NF regulations:
· Each value in the table can only be expressed once.
· Each line in the table should be unique (with unique keys).
• Non-bond information dependent on other keys should not be stored within the table.
Compliance with 3NF standards has the following features: There is a set of tables that store the associated data connected by the keys. For example, a 3NF database that stores customers and its related orders may have two tables: Customer and Order. The ORDER table does not contain any information for the order related to the customer, but a key value will be stored in the table, which points to the line of the customer information in the Customer table. Higher levels of standardization are also available, but it is better to be better? The answer is not necessarily. In fact, for some projects, even 3nf may introduce too high complexity to the database. - Lamont Adams
For efficiency, it is also necessary to standardize the table, which is also necessary. There have been a development of financial analysis software to use a non-standardized table to reduce the query time from an average of 40 seconds to two seconds. Although I have to do this, I will never use the non-standardized design concept of the data sheet as the design concept. The specific operation is just a derived. So if you anticipate non-standardized tables, it is entirely possible. - Epepke
7. Microsoft Access Report Tips
If you are using Microsoft Access, you can use the name of the user-friendly field name: such as
Customer Name instead of TXTCNAM. This way, when you create a form and report with a wizard, its name will make people who are not programmers easier to read. - jwoodruf
8. Indications that are not active or not
Add a field indicating whether the record is no longer active in the business. Whether it is a customer, what is the employee?
People, doing so can filter active or inactive status when you run inquiry. At the same time, it also eliminates some of the problems faced when using data, for example, some records may no longer be used for them, and then delete it. - theoden
9. Use role entities to define columns belonging to a certain category
When you need to define a specific category or a specific role, you can create a specific time-related relationship with a role entity, so you can implement self-documentation. The meaning here is not to let the Person entity comes with a title field, but said why don't you describe the person with a Person entity and a person_type entity? Then, when John Smith, Engineer is upgraded to John Smith, Director and even finally climbed to John Smith, CIO's high, and all you have to do is changing the key value between the two tableson and person_type, at the same time Increase a date / time field to know when the change occurs. In this way, your Person_Type table contains all Person possible types, such as Associate, Engineer, Director, CIO or CEO, etc.
There is also an alternative to changing the Person record to reflect changes in the new title, but this cannot track the specific time of the position where you are in place. - TEBURLEW
10. Nomencaster data using common entity
The easiest way to organize data is to adopt common names, such as: Person, Organization, Address, and Phone, etc. When you combine these commonly used general names or create a specific corresponding copile body, you get your own special versions. The main reason for adopting general terms at the beginning is that all specific users can be embodied in abstract things.
With these abstract representations, you can use your own special names in the 2nd level, for example, Person may be Employee, Spouse, Patient, Client, Customer, Vendor or Teacher, etc. Similarly, Organization may also be Mycompany, MyDepartment, Competitor, Hospital, Warehouse, Government, etc. Final Address can be specifically Site, Location, Home, Work, Client, Vendor, Corporate, and FieldOffice. Using general abstract terms to identify "things" categories allow you to achieve huge flexibility in association with business requirements, while doing so can significantly reduce the redundancy required for data storage. - TEBURLEW
11. Users from all over the world
When designing a database or other international characteristics, you must remember that most countries have different field formats, such as postal coding, etc., some countries, such as New Zealand, there is no postal code. - BILLH12. Data Repeat requires a discrete data sheet
If you find yourself in repeating data, create a new table and a new relationship. - alan rash
13. 3 useful fields that should be added each table
· DRECORDCREATIONDATE, default is now (), and getDate () in SQL Server under SQL Server
· SRecordcreator, under SQL Server, NOT NULL DEFAULT USER
· NRecordVersion, recorded version tag; help to accurately describe NULL data or loss data in the record
- Peter Ritchie
14. Use multiple fields to addresses and calls
It is not enough to describe the street address. Address_Line1, address_line2 and address_line3 can provide greater flexibility. Also, the phone number and email address are best to have its own data sheet, and it has its own type and tag category. - DWNERD
Over-standardization can be careful, doing so may result in problems in performance. Although address and telephone tables can usually reach the best, if you need to access such information frequently, you may be more appropriate to store "preferred" information (such as Customer et al.) In its parent table. Compromise between non-standardized and accelerated access is certain. - DHATTREM
15. Using multiple names fields
I feel very surprised, many people leave a field in the database. I think that only developers who have just been introduced will do this, but this practice is actually very common online. I suggest that the last name and name are treated as two fields, and then combine them again when querying. - Klempan
Klempan is not the only person noticed to use a single Name field, it is necessary to make this situation more friendly to users. I am most commonly created in the same table, which can be used automatically by automatically connecting the standardized field so that it will change when data changes. However, doing so when using modeling software. In summary, use the connection field to effectively isolate the user application and developer interface. - Damon
16. Break the name and special character of the case and special characters
One of the most annoyed things in the past is the name of the object in the database, such as CustomerData. This problem exists from Access to the Oracle database. I don't like to use this case-sensitive object naming method, and the result has to be manually modified. Think about it, can this database / application not to use a more powerful database? All uppercases and the names containing the names of the underwriting have better readability (Customer_Data), absolutely don't leave space between characters of the object name. - bfren
17. Be careful
To ensure that your field name is not conflict with the retention word, database system or common access method, for example, there is a table in an ODBC connection program I have written in the recent name, where DESC is used as a description field name. The consequences can imagine! DESC is the reserved word after the abbreviation of Descending. A SELECT * statement in the table is available, but I get it is a lot of information that is useless. - Daniel Jordan
18. Keep the word name and type of consistency
Be sure to ensure consistency when named the field and specify the data type. If the field is called "agreement_number" in a table, you don't change the name to "REF1" in another table. If the data type is an integer in a table, you can turn it in another table. Remember, you have done your own lives, others have to use your database. - setanta19. Carefully select the digital type
It is especially careful in SMALLINT and Tinyint types in SQL. For example, if you want to see the total monthly sales, your total field type is Smallint, then if the total amount exceeds $ 32,767 You can't make a calculation. - Egermain
20. Delete tag
In the table contains a "delete tag" field so that the row is marked as deletion. Do not delete a row separately in the relational database; it is best to use clear data programs and carefully maintain index integral. - KOL
21. Avoid using triggers
The functionality of the trigger can usually be implemented in other ways. The trigger may become interference when debugging the program. If you really need a trigger, you'd better focus on its documentation. - KOL
22. Contains version mechanism
It is recommended that you introduce version control mechanisms in the database to determine the version of the database in use. This requires this requirement anyway. For a long time, the needs of users will always change. It may eventually be required to modify the database structure. Although you can determine the version of the database structure by checking new fields or claims, I found that the version information is not more convenient in the database? . - Richard Foster
23. Leave the text field.
ID type text field, such as customer ID or order number, etc. should be set more than general imagination, because time is not long, you will be embarrassed because you want to add extra characters. For example, suppose your customer ID is 10 digits. Then you should set the length of the database table field to 12 or 13 characters. Is this a waste of space? There is a little, but there is so much that you imagine: A field extends 3 characters in 1 million records, plus a little index, but the entire database is more than 3MB of space. But this additional space can achieve the growth of database size without the need to refactor the entire database in the future. - TLUNDIN
Part 4 - Guarantee the integrity of data
1. Force data integrity with constraint rather than business rules
If you handle the needs in accordance with the business rules, you should check the business level / user interface: If the business rule changes, you only need to make an update.
If the demand stems from the need for maintenance data integrity, it is necessary to apply a restriction condition on the database level.
If you really use constraints in the data layer, you have to guarantee that there is a way to notify the user interface using the user's understanding of the language that cannot be understood by the cause of constraints. Unless your field name is very lengthy, the field name is not enough. - Lamont Adams
As long as it is possible, use the database system to implement the integrity of the data. This includes not only the integrity of standardization and also includes functionality of data. You can also increase the trigger to ensure the correctness of the data when writing data. Do not rely on the business layer to ensure data integrity; it does not guarantee the integrity of the table (foreign bond), so it is impossible to impose on other integrity rules. - Peter Ritchie
2. Distributed data system
For distributed systems, you should estimate the amount of data in the next 5 or 10 years before you decide whether all data is replicated in each site or to save data in a place. When you transfer the data to other sites, it is best to set some tags in the Database field. New your tags after you receive your data at the destination site. To make this data transmission, write down your own batch or scheduler running at a specific time interval without transferring data after every day. Local copy your maintenance data, such as calculation constant and interest rate, etc., setting the version number to ensure that data is exactly the same at each site. - Suhair Techrepublic
3. Forced instruction integrity
There is no good way to eliminate it after harmful data into the database, so you should remove it before it enters the database. Activate the integrity features of the database system. This allows the data to be cleaned to force the developer to put more time handling error conditions. - KOL4. Relationship
If there are many pairs of relationships between the two entities, and it is possible to translate into a multi-relationship, then you'd better set to multiple-to-many relationships at the beginning. From the existing multi-to-one, there are many more relationships than a lot of relationships than one. - CS Data Architect
5. Adopt view
In order to provide another layer of abstraction between your database and your application code, you can create a special view for your application without having to directly access the data table directly. This is also equal to providing more freedom when processing database changes. Gay Howe
6. Present the data and restore the plan
Consider the data-owned strategy and include your data recovery process in advance during the design process. It is possible to ensure that data identification of data dictionary to the user / developer can also ensure documentation on data source. Write an online update to "Update Query" for later data loss can be re-process updates. - KOL
7. Use the storage process to let the system do
Solved a lot of trouble to generate a highly integrity database solution, my team determines some of the functional groups of associated tables, providing a set of regular stored procedures to access each group to speed up the speed and simplify the customer code. Development. During this time, we found that the 3GL encoder sets all possible error conditions, such as the following:
Select cnt = count (*)
From [
