Database Design Guide
------------------
If the business's data is more necessary than life, the design is the most important part of the application. Data
The material sweat charging building of the library is also a special story in the university degree. However, as we repeatedly emphasized, good
Teachers are more than experienced teachings. So we have recently found some professionals who have a lot of knowledge of the database to give everyone some settings.
Tips and experience of the database. Our editor selected 60 best skills from 130 feedbacks, and put these
Skills have been written in this article, in order to facilitate indexing of its content 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 is 10 techniques that specialize in the correct usage of the primary key generated by the system, and when and how to index field
To achieve optimum performance, etc..
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.
Page 2 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
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 databases
The project is not established from the beginning; usually, there is always an existing system that meets specific needs (may not be real
Now automatic calculation). Obviously, existing systems are not perfect, otherwise you don't have to build a new system. However, 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 up
Some project design parameters, and the customer has evaluated these parameters, and the development environment is also reviewed in advance.
Working mode, wait until the final deployment is applied, I saw a few prompts on the terminal and then immediately forth in front of me.
! I took a toss for my ear for hours, I realized that the original network of this company ran two database applications,
And access to the network requires clear and strict user accounts and their access. Understand this, the problem is solved: only need
Use the customer's system. The lesson gives me this project is: Remember, if you are in this, such as Access or Interbase
Develop applications in a class public environment, be sure to go to the system inside the system to figure out how you face the environment.
thing.
- kg
2. Define the standard object naming specification
Be sure to define the naming specification of the database object. For database tables, it is necessary to determine the table name from the beginning of the project.
Is a single form. In addition, the alias for the table is defined simple rules (for example, if the table name is a word, the alias will take the word
The first 4 letters; if the table name is two words, each of the first two letters of two words form four letters long alias;
The name of the fruit table consists of 3 words, you may wish to take one from the first two words and then remove two from the last word.
Letters, the result is still an alias for 4 letters, and the rest is pushed into the work table, the table name can be prefixed.
The name of the application using the table is attached to the application of Work_. 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. List name
Standard prefixes and suffixs should be used. Another example, if there is a lot of "Money" fields in your table, you may wish to increase each column.
A _AMT suffix. Also, the date column is best taking DATE_ as a name.
- Richard
Check the naming specification between the table name, the report name, and the query name. You may soon be made by the names of these different database elements
Confused. If you insist on naming the different components of these databases, at least you should start at the beginning of these object names.
Different from prefixes such as Table, Query or Report.
- rrydenm
If Microsoft Access is used, you can identify objects with QRY, RPT, TBL, and MOD.
TBL_EMPLOYEES). I have used TBL to independent when I am dealing with SQL Server (or Oracle), but I
Identify stored procedures with sp_company (now using sp_feft_), because if I have found a better processing office
The law often saves several copies. I use UDF_ (or similar tags) when implementing SQL Server 2000.
Written function.
- TIMOTHY J. Bruce
Page 3 © CNet Networks Inc. 2002
Www.zdnet.com.cn/developer
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 you can easily process 2000 issues in the future. Many people tell me if I don't want to go.
Solving this problem, because it is too much trouble to process (this is a long time before the world's well-known Y2K). I said.
As long as the pre-plan will not encounter a big trouble. As a result, I only finished the procedure for two weeks. Because of pre-
The plan is good, and later Y2K issues have fallen to the lowest level (recently heard that the program is even 1995)
It is also running on the AS / 400 system, the only small problem that occurs is to remove the annotation fee from the code.
- generalist
4. Get data mode resource manual
People who are seeking example mode can read the book "Data Mode Resource Manual", the book by Len Silverston, W. H.
INMON and KENT Graziano are written, it is a book worthy of data modeling books. The book covers a variety of chapters
Data fields, such as personnel, institutions, and work performance, etc.
- 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 clear
What is the application design should be more flexible and how to avoid performance bottlenecks; secondly, you know that there is no prior
The user will be surprised as you when the required demand changes.
CHRISDK
Be sure to remember the lessons of the past! Our developers should also help each other by sharing their experiences and experience. Use
Households think they no longer need to support, we should also conduct this education in this area, we have all facing this
"The moment" I have to do this. "
- DHATTREM
6. Logic design before physically 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
To achieve a considerable logic level, you can usually understand all aspects of the database design.
- Chardove7. Learn about your business
Don't add anything in your ER (entity relationship) mode before you determine that the system is in your ER (entity relationship) mode before you meet your needs.
A data sheet (why, you have not mode it yet? Please refer to the skills 9). Understanding your business business can be developed later
Save a lot of time during the stage. 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 terminology and
They explain what you think and you are heard. At the same time, it should also be used, and the system will express the system.
number. This way you can make your customers correct your own understanding and do the next ER design.
- TEBURLEW
Page 4 © CNet Networks Inc. 2002
Www.zdnet.com.cn/developer
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 within each table
The primary key. Creating the ER chart and the data dictionary is really a bit of a little fee but is full of other developers to understand the entire design.
need. The sooner creation can help avoid the possibility of future, so that any people who understand the database are clear.
Where is data from the database.
- BGUMBERT
There is an important emphasis on the latest document, such as the ER chart, etc., it is not too much, which is useful to indicate the relationship between the table, and
According to the dictionary, the use of each field is illustrated and any alias that may exist. This is finished for documentation for SQL expressions.
Fully necessary.
- 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. Mode does not have to make
It is very complicated; even simply handwritten on a piece of paper. Just to ensure that the logical relationships on it can have in the future.
beneficial.
- Dana Daigle
10. From the input and output
When defining database tables and field requirements (input), first check existing or designed reports, queries, and views
(Output) To determine which of these outputs is necessary and fields. For a simple example: If the customer needs one
The report is sorted by postal coding, segmentation and summing, you have to ensure that a separate postal coding field is included without the postal
The code is in the address field.
- Peter.Marshall
11. Report Tips
To understand how users report data: batch or submit reports online? The time interval is daily, week, month,
Every quarter or annual? 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 search performance comparison
Low and 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
Continue, and often ask customers to ensure that their demand is still in the purpose of development. A unchanging truth is: "Only I
I saw that I know what I want "" inevitably leads to a lot of rework, because the database does not meet customers never write
The demand standard of the down. What is even worse is that your explanation of them is only yourself, and it may be completely wrong.
- kgilson
Page 5 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
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 I establish a system storage customer information, I tend to
Store the last name field in a separate data table, and the starting day and termination day, so you can track this
Data entry changes.
- Shropshire Lad
2. Use meaningful field name
I have participated in the development of a project, which has the program that inherited from other programmers, the programmer likes to use the screen.
The display data indicates the use of the field name, which is not good, but unfortunately, she also likes to use some strange naming method, naming it.
The combination of Hungarian naming and control serial number is used, 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
I have done it, such as Customer_Shipping_Address_Street_Line_1 I, although it is very illustrative, but no one is willing
Type such a long name, the specific scale is in your grasp.
- Lamont Adams
3. Name the prefix
If you have a lot of fields (such as firstname), you may wish to use a particular table prefix (for example
CuslastName "to help you identify the field.
- NotoriousDog
Time data should include the "Recent Update Date / Time" field. Time mark The reason for finding data issues, reopened by date
Treatment / overloading data and clearing 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 wants to access external data
Source (file, XML document, other databases, etc.), you might wish to store the corresponding connection and path information in the user interface support table
in. Also, if the user interface executes tasks such as workflow (send mail, print letterhead, modifying record status, etc.), then
Data that generates workflow can also be stored in the database. Pre-arrangement always needs to pay, but if these processes use
According to the way to drive rather than hard coding, then strategy changes and maintenance will be much easier. In fact, if the process is data driver
You can push a considerable responsibility to the user, by the user to maintain your workflow process.
- TDUVALL
6. Standardization cannot be over
For those who are unfamiliar with standardization (Normalization), standardization can ensure that the fields in the table are the most basic.
Elements, and this measure helps to 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, 3NF regulations
set:
Page 6 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
· 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,
There are two tables: Customer and ORDER for a 3nf database that stores customers and their relevant orders. ORDER table does not package
A key value is included in the table, but the key is stored in the table, which means that the customer information is included in the Customer table.
The one line.
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 financial analysis
The software's life is to reduce the query time from an average of 40 seconds to two seconds. Although I have to do this,
But 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 the table
The problem of re-standardization of non-standardization 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 with a wizard, your name will make those who don't
It is more readily read by the programmer.
- 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 the new user in adoption.
Some problems that are facing data, for example, some records may no longer be used for them, and can play when delete it.
Preventive role.
- theoden
9. Use role entities to define columns belonging to a certain category
When you need to define a particular category or a specific role, you can use a role entity to create a specific time level.
Linkage, so you can achieve self-documentation.
The meaning here is not letting the Person entity with a title field, but said why not use the Person entity and
Person_type entity to describe people? Then, in all, 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 changed two tables
The key value of the relationship between Person and Person_Type, and add a date / time field to know when it is changed.
occurring. In this way, your Person_Type table contains all Person's 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 is not possible to track at time.
The specific time of the individual position.
Page 7 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
- 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 specific corresponding copies, you get it.
Special versions you use themselves. The main reason for adopting general terms at the beginning is that all specific users can have abstract things.
Slim.
With these abstract representations, you can use your own special name in the 2nd level, for example, Person may be
Employee, SPOUSE, Patient, Client, Customer, Vendor or Teacher et al. same,
Organization may also be Mycompany, MyDepartment, Competitor, Hospital,
Warehouse, Government, etc. Final Address can be specific to Site, Location, Home, Work,
Client, Vendor, Corporate and FieldOffice, etc. Using general abstract terms to identify "things" categories allow you to get huge spirit in association with business requirements
Activity, while doing so can also significantly reduce the redundancy required for data storage.
- TEBURLEW
11. Users from all over the world
When designing a database or a database with other international features, you must remember that most countries have different fields.
For example, there is a country, such as the new Zealand, there is no postal code.
- Billh
12. Data Repeat requires 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 accurately explain NULL data or lost data in the record
because
- 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
To 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 categories.
- DWNERD
Over-standardization can be careful, doing so may result in problems in performance. Although address and telephone tables are usually achieved
Best state, but if you need to access such information, you may store "preferred" information in its parent table (for example
Customer et al.) Is more appropriate. Compromise between non-standardized and accelerated access is certain.
- DHATTREM
Page 8 © CNet Networks Inc. 2002
Www.zdnet.com.cn/developer
15. Using multiple names fields
I feel very surprised, many people leave a field in the database. I think it is only the developer who has just started.
Do it, but this practice is actually very common. I suggest that the last name and the name are treated as two fields, and then
When queried, combine them again.
- Klempan
Klempan is not the only one who noticed the use of a single Name field, it is better to make this situation more friendly for users.
Some methods. I am most commonly created in the same table, which can be automatically connected to the standardized field, this
When the sample data changes, it will change. However, doing so when using modeling software. In short, connect
Field mode can effectively isolate 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 question
The problem exists from Access to the Oracle database. I don't like to use this case-sensitive object naming method, and the result is not
Do not manually modify the name. Think about it, can this database / application not to use a more powerful database? All
Override and contains the names of the underlying symbols with better readability (Customer_Data), absolutely don't be in the object name
The blades are left space.
- bfren
17. Be careful
To ensure that your field name is not conflict with a retention word, database system or common access method, such as a recent recently prepared
There is a table in the ODBC connection program, where DESC is used as a description field name. The consequences can imagine! DESC is
Descending Retention after abbreviation. 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 in a table
"Agreement_number", you don't change the name to "REF1" in another table. If the data type is in a table
It is an integer, then don't become a word shape in another table. Remember, you have lived your own lives, others have to use your number.
According to the library.
- setanta
19. Choose a digital type carefully
It is especially careful to use Smallint and Tinyint type in SQL, for example, if you want to see the total monthly sales, your total word
The type of paragraph 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 separately in the relational database
A certain line; it is best to use the clear data program and carefully maintain the integral overall.
- KOL
Page 9 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
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 to pick
With triggers, 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. No matter how you have to achieve this
begging. For a long time, the needs of users will always change. It may eventually be required to modify the database structure. Although you can check
Check new fields or claims to determine the version of the database structure, but I found that the version information is stored directly into the database is not more
Is it? .
- 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
Most will be embarrassed because of the additional characters to be added. 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? It is a little, but it is not you imagined.
So many: A field lengthens 3 characters in 1 million records, plus a little index, but the whole data is not allowed
The library occupies 3MB space. But this additional space can achieve database gauges without future reconstruction of the entire database.
Growth.
- TLUNDIN
24. Column name skills
We found that if you give a unified prefix if you give a column name of each table, you will get bigger when writing SQL expressions.
Big simplified. This is also a shortcoming, such as destroying the role of the automatic table connection tool, and the latter will share the public column name.
According to the library, but even these tools are sometimes not connected. For a simple example, assume that there are two tables:
Customer and ORDER. The prefix of the Customer table is CU_, so the subparand name in the table is as follows: Cu_name_id,
Cu_Surname, Cu_Initials and Cu_Address, etc. The prefix of the ORDER table is OR_, so the subparand name is:
OR_RDER_ID, OR_CUST_NAME_ID, OR_QUANTITY, and OR_DESCRIPTION, etc.
This will be written as follows from the database from the database.
Select * from custom000, Order
Where cu_surname = "myname"
AND CU_NAME_ID = OR_CUST_NAME_ID
And or_quantity = 1; write this like without these prefix:
Select * from custom000, Order
Where customer.Surname = "myname"
And customer.name_id = Order.cust_name_id
And ORDER.QUANTITY = 1
The first SQL statement is not type how much characters are typed. But if the query involves 5 tables or more columns, you know this skill.
More useful.
- Bryce Stenberg
Page 10 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
Part 3 - Select key and index
1. Data mining should be pre-planned
My market sector once to deal with more than 80,000 contact information, and fill in the necessary data for each customer (this is definitely not small
live). I have to identify a group of customers as a market goal. When I started with the first design table and field, I tried it.
Increase too much field in the main index to speed up the running speed of the database. Then I realize a specific group query and information mining
Neither accurate speed is not fast. The results have to rebuild in the main index and incorporate data fields. I found some indicator plan.
When the key - when I want to create a system type lookup, use the number as the main index field? I can use fax numbers
Retrieving, but it is not important to me almost like the system type. Using the latter as the primary field, database update
After re-indexing and retrieving.
- HSCOVELL
The data index in the two environmental data warels (ODS) and data warehouses (DW) is different. In the DW environment
Under, you have to consider how the sales department organizes sales activities. They are not database administrators, but they determine
Key information. The designer or database staff should analyze the database structure to determine between performance and correct output.
Best conditions.
- TEBURLEW
2. Use the primary key generated by the system
This day is the same skill 1, but I think it is necessary to repeat everyone here. If you always use the database
The system generated key as the primary key, then you actually control the index integrity of the database. In this way, the database and non-manual mechanisms are
Effectively controls access to each line of storage data.
It is also advantageous to use the system generated key as the primary key: When you have a consistent key structure, it is easy to find logical defects.
- TEBURLEW
3. Decompose fields for indexing
In order to isolate the named field and contain a field to support user-defined reports, consider breakout other fields (or even primary keys) for its group
Entries can so that users can index them. The index will speed up the execution speed of SQL and report generator scripts. for example,
I usually create a report in the case of the SQL Like expression, because the Case Number field cannot be decomposed into
Year, Serial Number, Case Type and Defendant Code and other elements. Performance will also become. If the annual and type word
The segment can be broken down into index fields, then these reports will run more.
- RDELVAL
4. Key design 4 principle
• Create foreign keys for related fields.
· All keys must be unique.
· Avoid using a compound key.
· Foreign key is always associated with unique key fields.
- Peter Ritchie
Page 11 © CNet Networks Inc. 2002
Www.zdnet.com.cn/developer
5. Don't forget the index
Indexes are one of the most efficient ways to get data from the database. 95% of database performance issues can be used to get indexing technology
solve. As a rule, I usually use the unique group index for the logical primary key, using the system key (as a stored procedure)
The unique non-group index is non-group index to any foreign key column. However, the index is like a salt, too much vegetables are 篌. Do you have to consider how big the database is space, how to access, and whether these accesss are mainly used as read and write.
- TDUVALL
Most databases index automatically created primary key fields, but don't forget the index foreign key, they are often used, ratio
If you are running a query showing a record of the primary table and all the joint tables. Also, don't index the Memo / Note field, no
To index large fields (there are a lot of characters), making the index occupy too much storage space.
- GBRAYTON
6. Don't index common small tables
Don't set any keys for small data tables, if they often have this insert and delete actions. Insert and
Index maintenance of deletion operations may consume more time than scanning tables.
- kbpatel
7. Don't choose a social security number (SSN)
Never use SSN as the database of the database. In addition to privacy, it is necessary to know that the Government is increasingly promising.
SSNs are used as other purposes other than income, and SSN needs manual input. Never use the manual input key as the master
Key, because once you entered an error, you can do it is to delete the entire record and start from the beginning.
- TEBURLEW
When I was studying at college, I remember that SSN was also used as a student number at that time. Of course, I don't have it.
legal. And people also know that this is illegal, but they are used to it. Later, with the initiative of the identity crime
Add, my current university campus is painful from a large stall data to delete SSN.
- generalist
8. Don't use the user's key
When determining what the field is used as a table, you can be careful that the user will be edited. Usually don't
Select the user-editable field as the key. This will force you to take the following two measures:
• Implementing a restriction on the behavior of the user editing field after creating a record. If you do this, you may find your application
The order suddenly changes in business needs, and the user needs to edit those in editable fields lack sufficient flexibility. Use
Households after entering the data until the record is found to find a problem? Delete rebuild? If the record is not available
Does reconstruct users open?
· Propose some methods for detecting and correcting key conflicts. Usually, the cost of energy is getting it, but from the performance to do this.
The consideration is bigger. Also, the correction of the keys may force you to break through your data and business / user interface layers.
from.
So still override an old saying: your design should adapt to the user rather than letting users adapt to your design.
- Lamont Adams
The reason why the primary key is not updated is that the primary key implements the association between different tables in relational mode. such as,
The Customer Table has a primary key CustomerID, while the customer's order is stored in another table. ORDER table's primary key
Page 12 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
It is a combination of ORDERNO or ORDERNO, Customerid, and date. No matter which key settings you choose, you need it.
The Order table stores Customerid to ensure that you can find its order records for users.
If you modify Customerid in the Customer table, you must find all relevant records in the Order table.
Row modification. Otherwise, some orders will not belong to any customer - the integrity of the database is finished.
If the index integrity rule is applied to the table level, it is almost impossible if you do not write a large number of code and additional deletion records.
Change a record of a record and all associated records in the database. And this process is often incorrect, so it should be avoided as much as possible.
- ljboast
9. Optional button Sometimes you can make a primary key to remember, querying the data is not a machine but a person.
If you have an optional button, you may further use it for primary key. In that case, you have the ability to build powerful indexes. This
A person who can prevent the use of a database has to connect to the database to properly filter data. Database in strict control domain table
In this load, this load is more awake. If the option is really useful, it is the level of the primary key.
My opinion is that if you have an optional button, such as State_code in the country, you don't do the only key in existing can't change.
Create a subsequent key. You have to do more than creating a worthless data. For example, the following example:
SELECT Count (*)
From address, state_ref
WHERE
Address.State_Id = state_ref.state_id
And state_ref.state_code = 'TN'
My approach is this:
SELECT Count (*)
From address
WHERE
And State_code = 'TN'
If you build this form of association because of the subsequent keys of excessive use table, the operation load really needs to be considered.
- Stocker
10. Don't forget the foreign key
Most database indexes automatically created primary key fields. But don't forget the index outer key field, they are in the records in the primary table.
Each time it is related to its associated record. Also, don't index the Memo / Notes field and don't index large text fields
(Many characters) that makes your index occupies a large amount of database space. .
- GBRAYTON
Page 13 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
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 rules have changed
Chemical, then just need to be updated.
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 ensure that there is a way to use the user to understand the reason why the update cannot be confined.
The language notifies the user interface. 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, but also
Includes the functionality of data. You can also increase the trigger to ensure the correctness of the data when writing data. Don't rely on business level
Ensure data integrity; it cannot guarantee the integrity of the table (foreign bond), so it cannot be strongly added to other integrity rules.
- Peter Ritchie
2. Distributed data system
For distributed systems, you should save all data in each site or to save data in a place in a place.
It is estimated that the amount of data in the next five or 10 years. When you transfer the data to other sites, it is best in the database field.
Set some tags in. New your tags after you receive your data at the destination site. In order to carry out this data transmission, please write down
Your own batch or scheduler runs at a specific time interval instead of transferring data after every day. local
Copy your maintenance data, such as calculation constants and interest rates, setting the version number to ensure that the 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. Excitement
Indicates integrity features of live database systems. This allows the data to be cleaned to force developers to invest more.
Basic error condition.
- KOL
4. 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 build a specialized for your app
Views don't have to access the data table directly from the application. This is also equal to providing you with more when processing database changes.
free.
Gay Howe
Page 14 © CNET Networks Inc. 2002
Www.zdnet.com.cn/developer
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. Using you can release to users / development
Personal data dictionary achieves convenient data identification while ensuring documentation of data source. Write online update to "update query" for
In the future, the data loss can be rescooted.
- 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 decided to package some
The functional group of associated tables provides a set of regular stored procedures to access each group to speed up the speed and simplify the client code.
hair. During this time, we found that the 3GL encoder sets all possible error conditions, such as the following:
Select cnt = count (*)
From [
