A discussion on database design experience (citation)

Design, data, database, database design, database design experience

Author: water is cold

A successful management system is made up of: [50% of the Business + 50% software], and 50% of the success of the software has [25% database + 25% of the program], the database design is a good or bad is a key. 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.
Insert some database design tips:

First, design ideas
Designing a database application is not a very difficult thing for many programmers. But there are a lot of database application software can not be recognized by users, the reason is the early research, information design units and use of units did not get the corresponding ideological communication.
The communication here includes the user's requirements for software functions, time efficiency requirements, software platform requirements, price requirements and software maintenance requirements. These five requirements constitute a successful application of all the research projects of the software.
But the most important thing here is the requirement of the software function, different enterprise to the software requirements is not the same. The following is an overview of requirements for software functionality:
1. Object Sex:
This is not software engineering or other reference books described in the design requirements, but this is an inevitable trend of development. Our software is mainly started by financial software, the financial business process is the national unification stipulation, the retail financial process and the building materials industry financial business process is not much different, therefore designs one kind of software can apply the different company even is the Cross-industry company is also very normal one thing, but along with our country market economy development, With information technology to promote the development of enterprises into a practical and effective means, many different industries and even different enterprises in the same industry have different requirements for information application software.
In the modern process development technology, the face object technology is a big leap. But many of the database applications that have been developed do not realize this, so there is no market for the software developed. One time, a software salesman to my company to promote software, is Ming software company's personnel Management software, the company personnel department leadership is very interested, casually asked a few questions, one of which is there is no temporary management, one is the wage statistics query can be in accordance with the age of workers, posts, titles, education classification statistics. As a result, the software does not have these two functions, so the personnel department leaders politely rejected the application software salesman's request for demo software.
As a developer, it is quite common to add the above two features to a database application, but it is because the user's needs are not considered during development and the software sales fail.
So it's a necessary factor for a successful application to consider the object of software at the outset.
2. Ease of Use
The quality of usability is not determined by the development Department, nor is it determined by the software evaluation organization, but by the user. This is confirmed in the exchange of work.
Many software considerations, such as Oracle database for the backend database Oracle Company's ERP software solution, did not take into account China's national conditions, not only the application of complex interface classification, but also in the busy business, because the operation of complex often counterproductive, to delay the work, provoked the leadership complained, Workers complain, but not as well.
In the sales system software debugging process, I met a sales company salesman, he talked to me about the use of software after a lot of feelings. He said the software was supposed to lighten the workload, but the sales system had a very unfriendly application interface, in the network database input data, input data a lot, but the software requires a keyboard typing, a mouse click, the thousands of data input, people will use the keyboard a mouse, live like a pendulum, exhausted, Why not design can be controlled by the keyboard.
In fact, the software in the process of preparation must be more communication with the business staff, understand the work flow is very important, but must not ignore the ease of use in the entire software performance can not be ignored in the proportion.
3. Extensibility
As a part of modern software system, scalability is becoming one of the main functions of software life. No matter what companies want to buy software to adapt and meet the needs of the company's business development changes, but also want to be able to and other purchased software together to form a complete enterprise software system.
In software, this is a little difficult, because to meet this requirement not only to predict the direction of enterprise development, and in the software to set aside data exchange interface, in the application document to publish some of the database composition or even part of the source code.
But from the big application direction, we design the software must achieve this use the function. Kingdee, Ufida The two large software companies have implemented the Customer Development Toolkit to achieve the customer two development needs.
4. Maintenance features
Software maintenance is necessary to ensure that the software works properly. But discussion, who can not guarantee software in the failure of the software maintenance personnel can be timely maintenance, which requires the software design is to increase the software maintenance function. With software maintenance capabilities, even simple backup capabilities, can also be in the event of the loss of data to a minimum.

In addition to general functions, in software design, I think that the above four functions are to be added and perfected, so that we have made the database application software can have a higher use value.

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

So I summed up the past years to take the detour and experience, and on the Internet to find some of the database design accomplished professionals to teach you some design database skills and experience. The 60 best tips were selected and written 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 1th-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.
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 [fields] use a set of design rules for the key. For example, if the key is a numeric type, you can use _n as the suffix, and if it is a character type, you can use the _c suffix. You should use the standard prefix and suffix for the column [field] name. Again, if you have a lot of "money" fields in your table, you might add a _m suffix to each column [field]. Also, a date column [field] is best preceded by a d_ 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 are using Microsoft Access, you can identify objects (such as tbl_employees) with symbols such as qry, RPT, TBL, and mod. I used TBL to index tables when I was dealing with SQL Server, but I used Sp_company (now Sp_feft_) to identify the stored procedures, because sometimes I would save several copies if I found a better approach. I used udf_ (or similar tags) to identify the functions I wrote when I implemented SQL Server 2000.
3. Work for the benefit of its device
Using the ideal database design tools, such as: SyBase Company's powerdesign, she supports the PB, VB, Delphe and other languages, through the ODBC can connect to the market popular more than 30 databases, including DBase, FoxPro, VFP, SQL Server, etc. I will highlight the use of powerdesign in the future.
4. Access Data Mode Resource Manual
People looking for sample patterns can read the Data Mode resource manual, written by Len Silverston, W. Inmon and Kent Graziano, as 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. Other You can also refer to: [1] Shaman Xuan Wang Shan Database System Introduction (second Edition) Higher Education Press 1991, [2][United States] Steven M.bobrowski Oracle 7 with client/server computing technology from getting started to proficient in Liu Jianyuan and other translation Electronics publishing House, 1996, [ 3] Zhou-Zhong meta Information System modeling Method (next) Electronics and Informatization 1999, 3rd, 1999
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 when "it would have been so much better ...".
6. Logical design prior to physical practice
Make logical design before delving into the physical design. As a large number of case tools emerge and your design can reach a reasonably high level of logic, you will generally have a better understanding of all the aspects of database design as a whole.
7. Know Your Business
Do not add even one datasheet to your ER (Entity Relationship) pattern before you make sure that the system meets its needs from the customer's perspective (why, you don't have a pattern?) Then please refer to tip 9. Understand that your business can save a lot of time in the future development phase. Once you've identified your business needs, you can make a lot of decisions on your own.
Once you think you have identified the business content, you'd better have a systematic communication with the customer. Use the customer's terminology and explain to them what you think and what you hear. It is also necessary to express the relational cardinality of the system in terms of possible, will and must. This will allow your client to correct your own understanding and then do the next ER design.
8. Create a data dictionary and ER chart
Be sure to take some time to create ER charts and data dictionaries. At a minimum, you should include the data type for each field and the primary foreign key in each table. It's a bit time-consuming to create ER charts and data dictionaries, but it's absolutely necessary for other developers to understand the entire design. The earlier you create, the more you can help avoid the potential confusion that you face in the future, so that anyone who knows the database can be clear about how to get data from the database.
Having a recent document, such as an ER chart, is not too important to emphasize, which is useful for indicating the relationship between tables, and the data dictionary describes the purpose of each field and any aliases that may exist. This is absolutely necessary for the documentation of SQL expressions.
9. Create a pattern
A chart is more than words: A developer should not only read and implement it, but also use it to help himself and the user talk. Pattern helps to improve collaboration performance, so it is almost impossible to have big problems in the database design in advance. The pattern doesn't have to be complicated; it can even be written on a piece of paper. Just to ensure that the logical relationship in the future can produce benefits.
10. From the input and output to begin
When defining database tables and field requirements (input), you should first examine existing or designed reports, queries, and views (outputs) to determine which tables and fields are necessary to support these outputs. A simple example: if a customer needs a report to sort, segment, and sum the ZIP code, make sure to include a separate ZIP code field instead of Leelawadee the ZIP code into the Address field.
11. Reporting Skills
To understand how users typically report data: Batch processing or online submission of reports? Is the time interval daily, weekly, monthly, quarterly, or yearly? You can also consider creating a summary table if you want. System-generated primary keys are difficult to manage in the report. Users often return many duplicate data in a table with a system-generated primary key by using a key to retrieve them. This kind of retrieval performance is relatively low and can easily cause confusion.
12. Understand customer needs
This should seem obvious, but the demand is from the customer (from the perspective of both internal and external customers). Do not rely on the user to write down the needs of the real demand in the customer's head. You need to let customers explain their needs, and as development continues, ask customers frequently to ensure that their needs are still being developed. A constant truth is that "only when I see it do I know what I want" is bound to lead to a lot of rework, because the database does not meet the requirements that customers have never written down. And even worse, your interpretation of their needs is only yours, and may be completely wrong.