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.
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. such as,
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. 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
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
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.
- Chardove
7. 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
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
