Database Design-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

To develop applications in a public environment, you must go into the system to find 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
First 4
Letters. If the table name is two words, take the first two letters of each word to form an alias with four letters. For example:
If the table name is 3
You can take one of the first two words and then two more words from the last one.
The result is still composed of 4 letters.
The alias of the letter length, and so on) for the Working Table, the table name can be prefixed.
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
_ Code suffix. Column name
Standard prefixes and suffixes should be used. For another example, if your table contains many "money" fields, you may add
One _ 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 table, query, or report
Prefix.
-Rrydenm

If Microsoft Access is used, you can use qry, RPT, TBL, and Mod.
To identify objects (such
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. I am implementing SQL
When Server 2000 is used, the UDF _ (or similar mark) is used to mark my compilation.
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
Plan well, later Y2K
The problem has minimized the harm to the system (I recently heard that the program was used even in 1995 ).
It also runs on the AS/400 system. The only small problem that occurs is that it takes some time to delete comments from the code ).

-Generalist

4. Getting data mode Resource Manual
If you are looking for a sample mode, you can read the data mode Resource Manual, which is 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 you are clear
For 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