Database Design Guide 1)

Database Design Guide
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. Related Data
The materials designed by the database are full of resources, and university degree courses are also detailed. However, as we have repeatedly stressed
The teacher does not have much experience to teach. So we recently found some experts who are well versed in Database Design to teach you some design
Skills and experience in database computing. Our edits selected 60 of the 60 best tips from the 130 feedbacks we received, and
The tips are written in 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? Here are 10 tips specifically for the correct usage of the primary key generated by the system, and when and how to index fields.
To obtain the best 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.
Page 2 CNET Networks Inc. 2002
Www.zdnet.com.cn/developer

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 Databases
Projects are not built from scratch. Normally, there will always be existing systems within the Organization to meet specific needs (there may be no real
Automatically calculated ). Obviously, the existing system is not perfect, otherwise you do not have to create a new system. However, the research on the old system
This allows you to discover some minor issues that may be ignored. In general, it is absolutely good for you to evaluate the existing system.
-Lamont Adams

I once took over a database project developed for a regional transportation company. It is not difficult to work, but access databases are used. My settings
Some project design parameters are provided, and these parameters are evaluated with the customer.
When the application is finally deployed, I can only see a few prompts on the terminal, and then immediately put it in front of me.
Now! After several hours, I realized that the company ran two database applications on the network,
Network access requires clear and strict user accounts and their access permissions. To understand this, the problem can be solved:
Use the customer's system. The lesson of this project is: Remember, if you
Develop applications in a public environmentProgramMake sure to go deep into the system to figure out how the environment you are facing returns.
Events.
-Kg

2. Define standard object naming rules
You must define the naming rules for database objects. For database tables, it is necessary to determine from the beginning of the project that the table name is in the plural
Is a singular form. In addition, a simple rule should be defined for the table alias (for example, if the table name is a word, the alias will take the word
For example
If the table name is composed of three words, you can extract one from the first two words and then two from the last word.
The result is a 4-letter alias, and the rest are the same.) for working tables, you can add a prefix to the table name.
The name of the application that uses the table is attached to work. 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. Column name
Standard prefixes and suffixes should be used. For another example, if your table contains many "money" fields, you may add
A _ AMT suffix. Also, we recommend that you use date _ as the name for the date column.
-Richard

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.
Confused. If you insist on naming different components of these databases in a unified manner, at least you should start with the names of these objects.
Use a prefix such as table, query, or report.
-Rrydenm

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
Use sp_company (currently use sp_feft _) to identify the stored procedure, because sometimes I find it easier to handle it.
Methods often save several copies. When implementing SQL Server 2000, I used UDF _ (or similar tag) to identify my encoding.
Write function.
-Timothy J. Bruce

3. Pre-plan
In the early 1980s s, I was still using the asset account system and system 38 Platform, When I was responsible for designing the date
Field, so that you can easily handle the 2000 Issue in the future without any effort. Many people tell me not to go
Solve this problem because it is too difficult to handle it (it was a long time before the Y2K problem was well known ). I replied
As long as you plan ahead, there will be no major troubles in the future. As a result, it took me two weeks to complete all the procedures. Because
The plan was good, and later the Y2K problem reduced the harm to the system to a minimum (I recently heard that the program was even in 1995 ).
It is also running on the AS/400 system. The only small problem that occurs is fromCode).
-Generalist

4. Getting data mode Resource Manual
People looking for sample modes can read the book data mode Resource Manual, written by Len silverston, W. H.
Written by inmon and Kent Graziano, it is the best data modeling book worth having. This book includes many chapters
Data fields, such as personnel, institutions, and work efficiency.
-Minstrelmike

5. Imagine the future, but never forget the lessons of the past.
I found it useful to ask users about future demand changes. This can achieve two goals: first, you can understand
Know where the application design should be more flexible and how to avoid performance bottlenecks. Secondly, you know that there is no
The user will be as surprised as you are when determining the demand change.
-Chrisdk

Remember the lessons learned from the past! Our developers should also share their experiences and experiences to help each other. Ready for use
They think that they no longer need any support. We should also educate them in this regard.
"How nice it would have been to do this at the beginning ".
-Dhattrem

6. Logical design before physical practice
Logical design is required before going deep into physical design. With the emergence of a large number of CASE tools, you can also design
To reach a fairly high level of logic, you can generally better understand all aspects of database design.
-Chardove

7. Understand your business
Before you fully determine that the system meets your needs from the customer's perspective, do not add any
A data table (why, you have no mode yet? Refer to tip 9 ). Understanding Your enterprise business can be developed in the future
The phase saves a lot of time. Once you have defined your business needs, you can make many decisions on your own.
-Rangel

Once you think that you have already defined the business content, you 'd better have a system discussion with the customer. Use the customer's terminology and
They explain what you think and what you hear. At the same time, it is necessary to use words such as possibility and necessity to express the relationship basis of the system.
Number. In this way, you can let your customers correct your own understanding and then do a good job in ER design.
-Teburlew

8. Create a data dictionary and ER chart
It takes some time to create er charts and data dictionaries. At least the Data Type of each field and
. Creating er charts and data dictionaries is indeed time-consuming, but it is absolutely necessary for other developers to understand the entire design.
Yes. The sooner the database is created, the more difficult it will be to avoid potential confusion in the future, so that anyone familiar with the database can be clear
How to obtain data from the database.
-Bgumbert

There is a new document, such as er chart, which emphasizes the importance of not much, which is useful for displaying the relationship between tables, while
Data Dictionary describes the purpose of each field and any alias that may exist. This is the end of the docalization of SQL expressions.
All necessary.
-Vanduin. Chris. CJ

9. Creation Mode
A chart is better than a thousand words: developers not only need to read and implement it, but also use it to help themselves talk to users.
The mode helps improve the efficiency of collaboration, so that there is almost no big problem in the early database design. Mode is not required
It's complicated. You can even simply write it on a piece of paper. It is only necessary to ensure that the logical relationship on it will take effect in the future.
Benefits.
-Dana Daigle

10. Start with 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 the customer needs
The report is sorted, segmented, and summed according to the zip code. Make sure that the report contains separate zip code fields instead of postal code fields.
The code is entered into the address field.
-Peter. Marshall

11. Report skills
How do users report data: batch processing or online report submission? The time interval is daily, weekly, monthly,
Quarterly or annually? You can also create a summary table if necessary. The primary keys generated by the system are difficult to manage in the report.
When you search tables with primary keys generated by the system, many duplicate data is often returned. Comparison of such search performance
Low and easy to cause confusion.
-Kol

12. understand customer requirements
This should seem obvious, but the demand is from the customer (from the perspective of internal and external customers ).
Do not rely on the requirements written by users. The real requirements are in the customer's head. You need to ask the customer to explain their needs, and
Continue to send, but also frequently ask the customer to ensure that their needs are still under development. The unchanging truth is: "only me
When I saw it, I realized what I wanted. "It will inevitably lead to a lot of rework, because the database has never been written to the customer.
Requirements Standards. What's worse is that your explanation of their needs only belongs to you and may be totally wrong.
-Kgilson

Part 2-Design tables and fields
1. Check for various changes
When designing a database, I will consider which data fields may change in the future. For example, the last name is like this (note
It refers to the surname of a westerners, for example, the surname of a female after marriage ). Therefore, when establishing a system to store customer information, I tend
Store the Last Name field in a separate data table, and add fields such as the start date and end date to track this field.
Change of data entries.
-Shropshire glad

2. Use meaningful field names
One time I participated in the development of a project with programs inherited from other programmers, the programmer liked to use the screen
It is not a good idea to display data indicator naming fields, but unfortunately, she also prefers some strange naming methods.
A combination of Hungary naming and serial number control, such as cbo1, txt2, and txt2_ B.
Unless you are using a system that only targets your abbreviated field name, please try to clarify the field description as much as possible. Of course.
Too much. For example, although customer_shipping_address_street_line_1 I is descriptive, no one wants
Enter such a long name.
-Lamont Adams

3. prefix naming
If multiple tables have multiple fields of the same type (such as firstname), you may use the prefix of a specific table (such
Cuslastname) to help you identify fields.
-Notoriousdog

Timeliness data should include the "Last updated date/time" field. The time mark is used to locate the cause of the data problem and relocate the problem by date.
It is particularly useful to manage/reload data and clear old data.
-Kol

5. Standardization and data-driven
Data Standardization not only facilitates yourself but also facilitates others. For example, if your user interface needs to access external data
Source (files, XML documents, other databases, etc.), you may wish to store the corresponding connection and path information in the user interface support table
. In addition, if the user interface executes tasks such as workflows (sending emails, printing letterhead, and modifying record statuses ),
Workflow data can also be stored in the database. There is always a need to make effort in advance, but if these processes adopt
Data-driven, not hard-coded, makes policy change and maintenance much easier. In fact, if the process is data-driven
, You can put a considerable amount of responsibility to the user, the user to maintain their own workflow process.
-Tduvall

6. standardization cannot overhead
For those who are not familiar with the normalization term, standardization can ensure that the fields in the table are the most basic.
And this measure helps eliminate data redundancy in the database. There are several forms of standardization, but third normal
Form (3nf) is generally considered to have the best balance in terms of performance, scalability, and data integrity. In short, 3nf rules
Set:

· Each value in the table can be expressed only once.
· Each row in the table should be uniquely identified (with a unique key ).
· The table should not store non-key information dependent on other keys.
Databases that comply with the 3nf standard have the following features: a group of tables specifically store the 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 is not a package.
Contains any information about the customer associated with the order, but the table stores a key value that points to the customer table containing the customer information
.
There are also higher levels of standardization, but will higher standards be better? The answer is not necessarily. In fact, for some projects
Even 3nf can introduce too much complexity to the database.
-Lamont Adams

For the sake of efficiency, table standardization is sometimes necessary. There are many such examples. There was a development finance Analysis
A non-standardized table is used to reduce the query time from an average of 40 seconds to about two seconds. Although I have to do this,
However, I will never regard Non-standardization of data tables as a design concept of course. The specific operation is just a derivation. So if the table
It is entirely possible to re-generate a non-standardized table.
-Epepke

7. Microsoft Access Report skills
If you are using Microsoft Access, you can replace the numbered name with a user-friendly field name. For example
Customer name replaces txtcnam. In this way, when you use a wizard to create a form and report, its name will make
It is easier for programmers to read.
-Jwoodruf

8. inactive or inactive indicators
Add a field to indicate whether the record is no longer active in the business. Customers, employees, and others
This will help you filter the active or inactive status when you run the query again. It also eliminates the need for new users
Some problems encountered during data deletion. For example, some records may not be used by them any more.
Preventive effect.
-Theoden

9. use a role entity to define a column of a certain type
when defining a thing of a specific category or role, you can use the role entity to create a specific time-level
link to achieve self-documentation.
the meaning here is not to allow the person entity to carry the title field, but to describe the person without the person entity or the
person_type entity? Then, for example, when John Smith and engineer are promoted to John
Smith, ctor and finally to the high position of John Smith and CIO, all you need 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 occurred
. 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.
.
-teburlew

10. Use the data of common entity naming organizations
The simplest way to organize data is to use common names, such as person, organization, address, and
Phone and so on. When you combine these common names or create specific sub-entities, you get
Your own special version. At the beginning, the main reason for using general terms is that all specific users can have abstract things
Physical.
With these abstract representations, you can use your own special names in the 2nd-level identifiers. For example, person may be
Employee, spouse, patient, client, customer, vendor, or teacher. Similarly,
Organization may also be mycompany, mydepartment, competitor, hospization,
Warehouse, government, etc. The last address can be site, location, home, work,
Client, vendor, and fieldoffice.
The use of general abstract terms to identify the category of "things" allows you to gain tremendous flexibility in associating data to meet business requirements.
In addition, this can significantly reduce the amount of redundancy required for data storage.
-Teburlew

11. users from all over the world
When designing databases that use networks or other international features, remember that most countries have different fields.
For example, in some countries, such as New Zealand, there is no postal code.
-Billh

12. Separate data tables are required for data duplication.
If you find that you are inputting data repeatedly, create a new table and a new relationship.
-Alan rash

13. Three useful fields should be added to each table
· Drecordcreationdate. In VB, the default value is now (), and in SQL Server, the default value is getdate ()
· Srecordcreator, which defaults to not null Default User under SQL Server
· Nrecordversion indicates the record version. It helps to accurately describe the original data that contains NULL data or lost data in the record.
Because
-Peter Ritchie

14. Use multiple fields for addresses and telephones
It is not enough to describe the street address in just one line. Address_line1, address_line2, and address_line3
To provide greater flexibility. In addition, it is best to have your own data table with your own phone number and email address.
And tag category.
-Dwnerd

Be careful about over-standardization, which may cause performance problems. Although the separation of address and phone table can usually achieve
The best status, but if you need to frequently access this type of information, you may store the "preferred" information in the parent table (such
Customer. The compromise between non-standardization and accelerated access is of some significance.
-Dhattrem

15. Use multiple name fields
I am surprised that many people leave a field for name in the database. I think this is only available to beginners.
But in fact, this approach is very common online. We recommend that you treat the last name and first name as two fields.
Combine them during query.
-Klempan

Klempan is not the only person who notices that a single name field is used. It is better to make this situation more user friendly.
Methods. I usually create a computing column in the same table and use it to automatically connect the standardized field.
It also changes when the data changes. However, this is a clever way to use modeling software. In short, connect
Fields can effectively isolate user applications and developer interfaces.
-Damon

16. Use case-insensitive object names and special characters
One of the most annoying things in the past is that the database has a mix of case-insensitive object names, such as customerdata. This question
Questions from access to Oracle Database exist. I don't like the case-insensitive object naming method.
You do not need to manually modify the name. Think about it, can such databases/applications mix into the day when more powerful databases are used? Full adoption
The name with uppercase letters and lower delimiters is more readable (customer_data ).
Leave spaces between characters.
-Bfren

17. Retain words with caution
Make sure that your field name does not conflict with the reserved words, database systems, or common access methods. For example
There is a table in the ODBC Connection Program, where DESC is used as the description field name. The consequences can be imagined! Desc is
The reserved word after descending. A select * Statement in the table is usable, but I get a lot
Useless information.
-Daniel Jordan

18. Maintain the consistency of Field Names and types
Ensure consistency when naming a field and specifying its data type. Assume that a field is called
"Agreement_number", you should change the name to "ref1" in another table ". Assume that the data type is in a table
Is an integer, so do not change it to the struct type in another table. Remember, when you finish your work, others need to use your data.
Database.
-Setanta