Database design Guidelines (1)

The database design is the most important part of the application if the data of the enterprise is compared to the blood necessary for life. There are numerous materials on database design, and there are also special lectures in university degrees. However, as we have repeatedly stressed, a good teacher is more than a lesson in experience. So I've been looking for some expertise in database design to teach you some skills and experience in designing databases. I've selected 60 of the best tips from the 130 feedback I've received and written them into this article for easy indexing of its content into 5 parts:

Part 1th-Before designing the database

This section lists 12 basic techniques, including naming conventions and clarifying business requirements.

Part 2nd-Design Database tables

A total of 24 guidance tips covering field design in the table and common problems that should be avoided.

Part 3rd-Select key

How to choose the key? Here are 10 techniques that specifically relate to the correct usage of system-generated primary keys, and when and how to index fields for optimal performance.

Part 4-Ensuring data integrity

Discuss how to keep the database clear and robust, and how to minimize harmful data.

Part 5th-Various tips

Other techniques that are not included in the 4 sections above are diverse, and they want your database development work to be easier.

--------------------------------------------------------------------------------

Part-Before designing the database

1. Review of existing environment

When designing a new database, you should not only study the business requirements carefully but also examine the existing systems. Most database projects are not built from scratch; there are always existing systems in the organization that meet specific requirements (and may not implement automated computations). Obviously, the existing system is not perfect, otherwise you won't have to build a new system. But the study of the old system allows you to find some subtle problems that may be overlooked. In general, it is absolutely good for you to look at existing systems.

I once took over a database project developed for a regional transportation company, and it's easy to live with an Access database. I set up some project design parameters, and with customers to evaluate these parameters, in advance also looked at the development environment to take the mode of work, wait until the final deployment of the application, I saw a terminal on a few prompts and immediately before I die! Scratching for several hours, I realized that the company's network ran two database applications, and access to the network requires a clear and strict user account and access rights. Understand this, the problem is solved: simply adopt the customer's system. The lesson of this project is: Remember, if you're developing applications in a public environment such as access or interbase, be sure to go deep inside the system and figure out what's going on in your environment.

2. Defining the standard object naming conventions

Be sure to define the naming conventions for database objects. For a database table, it is important to determine from the beginning of the project whether the table name is in plural or singular form. You also have to define a simple rule for the alias of the table (for example, if the table name is a word, the alias takes the first 4 letters of the word, and if the table name is two words, it takes the first two letters of two words to form a 4-letter alias; If the table's name is made up of 3 words, You might as well take one from the first two words and then remove two more letters from the last word, and the result is a 4-letter alias, and so on. For work tables, the table name can be prefixed Work_ with the name of the application using the table appended. The columns in the table take a set of design rules for the key. For example, if the key is a numeric type, you can use _no as the suffix, and if it is a character type, you can use the _code suffix. A standard prefix and suffix should be used for column names. Again, if you have a lot of "money" fields in your table, you might add a _amt suffix to each column. Also, the date column should be preceded by a date_ name.

Check the naming convention between the table name, report name, and query name. You may soon be confused by the names of these different database elements. If you insist on uniformly naming the different components of these databases, at least you should distinguish them by prefixes such as table, query, or the name of the header at the beginning of the names of those objects.

If you use Microsoft Access, you can identify objects with symbols such as qry, RPT, TBL, and mod (for example,

Tbl_employees). I also used TBL to index tables when I was dealing with SQL Server (or Oracle), but I used Sp_company (now Sp_feft_) to identify the stored procedures, because sometimes if I found a better approach, I would save several copies. I used udf_ (or similar tags) to identify the functions I wrote when I implemented SQL Server 2000.

3. Plan ahead

In the early 80, I was still using the asset account system and the system 38 platform, when I was responsible for designing all the date fields so that I could easily handle the 2000 problem in the future without any effort. A lot of people told me not to solve the problem, because it is too troublesome to deal with (this is a long time before the world known Y2K problem). I fought back and said that there would be no big trouble if we planned ahead. It took me only two weeks to change the program. Because of the planned good, later the Y2K problem to the system's harm to the lowest degree (recently heard that the program even in 1995 years are still running on the as/400 system, the only small problem is to remove the comments from the code for a bit of effort).

4. Access Data Mode Resource Manual

People looking for sample patterns can read the Data Model Resource manual, written by Len Silverston, W.h.inmon, and Kent Graziano, the best data modeling book worth having. The book includes chapters covering a wide range of data areas, such as people, institutions, and work efficiency.

5. Imagining the future, but not forgetting the lessons of the past

I find it useful to ask users how they think about future changes in demand. This can be done for two purposes: first, you can clearly understand where the application design should be more flexible and how to avoid performance bottlenecks, and second, you know that users will be as surprised as you are when there is no predetermined requirement change.

Be sure to remember the lessons of the past! We developers should also help each other by sharing their experiences and experience. Even if the user thinks they don't need any more support, we should educate them about it, and we've all faced the moment "how good would it be to do that?"