How to design a good database

Jun 30 posted in Oracle (RSS), 684 views.

Database design (Database Design) means that for a given application environment, the optimal structure of the database schema, the establishment of databases and applications, to enable them to effectively store data to meet various application needs of users (information requirements and processing requirements).

The field in the database, often the use of various types of database systems are collectively referred to as database applications.

First, databases and information systems (1) database is the core of information systems and infrastructure, information systems to large amounts of data in the model according to a certain organization, providing storage, maintenance, data retrieval, so that information systems can be convenient, timely, accurately from the database to obtain the necessary information.
(2) Information System database is whether the various parts together closely, as well as how to combine the key.
(3) database design and development of information systems is an important part of the building.
(4) database design should have the skills and knowledge:
Basic knowledge of databases and database design techniques of the basic knowledge of computer science and programming methods and techniques of software engineering principles and methods of application of knowledge in the field of

Second, the characteristics of database design database construction is the hardware, software and the combination of stem pieces of two-thirds of technology, seven management, basic data Twelve technology and management interface called the "dry matter"
Database design and application of system design should be a combination of structural (data) design: the design of the database or the database structure the framework of behavior (processing) design: the design of applications, transaction processing, such as the separation of the structure and behavior of software engineering design of the traditional neglect of data on the application of semantic analysis and abstraction, as long as possible the design of data structures as much as possible to defer the decision-making early commitment to data model database design and modeling studies, ignoring the acts of the design of the figure:

Third, the database design manual test on the quality of design together with the design and level of experience is directly related to the lack of scientific theory and engineering methods to support, engineering, it is difficult to guarantee the quality of the database is running again after a period of time often find a variety of different levels problem and increase the cost of maintaining manual design specification to design the basic idea and step by step process of iterative refinement design specification (continued)
Typical methods:
(1) New Orleans (New Orleans) Methods: The database design is divided into four stages SBYao Methods: The database design is divided into five steps IRPalmer methods: the database design as a process step by step (2) computer-aided design ORACLE Designer 2000
SYBASE PowerDesigner

Fourth, the basic steps of database design database design <br /> process (six phase)
1. Needs analysis phase
Accurate understanding and analysis of user needs (including data processing)
Is the basis of the entire design process is the most difficult and time-consuming step 2. The concept of the structure of the design phase
Design is the key to the entire database of user needs through a comprehensive, summarized and abstract form a specific DBMS independent of the concept of model 3. The logic of the structure of the design phase
The concept of the structure will be converted to a DBMS data model supported by their optimized 4. Database physical design stage
Logical Data Model to select the most suitable environment for the physical structure (including storage structures and access methods)
5. Database implementation phase
DBMS data provided by the use of language, tools, and the host language, according to the logic design and physical design of the outcome of the establishment of a database, developing and debugging applications, organizations, data warehousing, and 6 trials. Database operation and maintenance phase
Database application system is ready for trial operation in the formal run.
Process in the database system must be continually evaluated, adjusted and modified design features:
During the design process, the database design and data processing of the database design to closely combine the two aspects of the needs analysis, abstraction, design, implementation at all stages at the same time, cross-reference and complement each other in order to improve both design

Various stages of the design process to describe the design:
Figure:

Fifth, the database model of the formation of levels 1. Needs analysis phase: the application of the comprehensive needs of individual users
The need to belong to specific categories or with a specific role in the definition of things to do, the role of entities can be used to create a specific time relationship, which can be self-documenting.
For example: PERSON entities and used to describe the staff PERSON_TYPE entities. For example, when John Smith, Engineer upgraded to John Smith, Director and even the final climb John Smith, CIO of the high, and all you have to do is change the two tables PERSON_TYPE the relationship between the PERSON and the keys, while an additional Date / Time field to know when change is occurring. In this way, your PERSON_TYPE table PERSON includes all possible types, such as Associate, Engineer, Director, CIO or CEO and so on. There is also an alternative way is to change the PERSON title records to reflect the new changes, but so that in time the individual can not track the location of a specific time.
7) Select the number of types and text types in SQL as much as possible the use of adequate smallint and tinyint type to be especially careful. For example, if would like to see total sales on a total field type is smallint, then, if the total amount of more than $ 32,767 can not be calculated on the operation.
And the ID type of the text field, such as customer ID or order number, etc. should be set greater than generally imagined. Assuming a 10-digit customer ID long. Then you should be the length of the database table field is set to 12 or 13 characters long. However, this additional space occupied has no future reconstruction of the entire database can be achieved on the scale of the growth of the database.
8) increase in the deletion of fields in the table tag contains a "Delete Marked" field, so that line can be marked for deletion. Relations not in a separate database to delete a line; the best procedures used to clear the data but also to carefully safeguard the overall index.

3. Selection keys and indexes (logical database design)
Button to select the principles:
1) 4 key design principles to create a foreign key related fields. •
All keys must be unique. •
Avoid the use of composite keys. •
Foreign key is always associated key fields only. •
2) the use of system-generated primary key when designing the database using system-generated key as the primary key, then the actual control of the index of the integrity of the database. In this way, databases and Non-manual effective control mechanism to store data in each line of the visit. The use of system-generated key as a primary key advantages: when the keys have the same structure, can easily find a logical flaw.
3) Do not use the user's key (not primary key can be updated with the of)
In determining what the key field as a table when the user may have to be careful to edit the field. Usually do not choose the circumstances of the user can edit the fields as a key.
4) an optional master key can sometimes be key to bond further with an optional master key, can have the ability to build a strong index.

Index of use of principles:
Index is the data from the database to obtain one of the most efficient manner. 95% of database performance problems can be solved using indexing technology.
1) the logic of the primary key to use the only group index, keys on the system (as stored procedures) using only non-group index, foreign key column for any non-index group. Consider how much space a database table to visit, and these visits are mainly used for reading and writing.
2) the majority of databases are created automatically index the primary key field, but do not forget the foreign key index, which is frequently used keys, such as running queries show the main table and all associated records in a table on the useful.
3) Do not index memo / note field, not to index a large field (there are many characters), so the index will be taking up too much storage space.
4) Do not index small tables commonly used for small data tables not set any keys, if they often have to insert and delete operations do not even made. Of these insertion and deletion may be more than the maintenance of the index table space scan consume more time.

4. Data integrity design (logical database design)
1) the integrity of the realization of mechanisms:
Entity integrity: the primary key referential integrity:
Father of the table to delete data: cascade delete; restricted deleted; home space to insert the value of the parent table data: Insert restricted; recursive insert the parent table to update data: update cascade; limited update; home DBMS null value of the light There are two ways can the integrity of the realization: the realization of foreign key mechanism (bound by the rules) and the trigger mechanism to achieve the integrity of user-defined:
NOT NULL; CHECK; Trigger 2) business rules rather than mandatory constraints data integrity using the database system data integrity. This will not only be achieved through standardization of data integrity but also functional. When writing data can also trigger an increase to ensure the accuracy of the data. Do not rely on the business layer to ensure data integrity; it can not guarantee that forms between the (foreign keys) can not be imposed on the integrity of the integrity of the rules on the other.
3) the integrity of mandatory instructions
Harmful data in the database before entering removed. The instructions to activate the integrity of the database system characteristics. This data can be maintained clean and will force developers to devote more time to deal with error conditions.
4) Find a control data integrity data integrity control is the best way to limit user choice. Whenever possible should be made available to the user a clear choice for the value of the list. This type of code will reduce errors and misunderstandings while providing data consistency. Particularly suitable for certain public data to find: country code, such as a status code.
5) In order to view the use of databases and applications with another layer of abstraction between the code, the application can view the establishment of specialized applications without having to go direct access to data sheets. It could also mean changes in dealing with the database to provide you with more freedom.

5. Other design techniques 1) to avoid the use of the functions of flip-flop flip-flop can usually achieve by other means. Flip-flop in the debugging process could be interference. If you do need the use of flip-flop, you'd better concentrate on its documentation.
2) the use of common English (or any other language) and do not use code to create drop-down menu, a list of the best statements in accordance with the sort of English. If the need for coding, coding can be attached next to the user to know English.
3) commonly used to preserve information stored so that a table devoted to the general database information very useful. Exterior storage in the current version of the database, the latest inspection / repair (for Access), the name associated design documents, customer and other information. This simple mechanism to achieve a tracking database, when a customer complained that they hope that the database does not meet the requirements of contact with you when to do so for non-client / server environment is particularly useful.
4) contains the version of the mechanism introduced in the database version control mechanism to determine the version of the database. Over time, the needs of the users will always be change. Ultimately may be asked to modify the database structure. The version information stored directly to the database more convenient.
5) the preparation of documents for all the shortcuts, named norms, constraints and functions should be the preparation of the document.
Used to the table, columns, triggers and other database tools plus notes. Development, support and follow-up very useful modification.
Documentation on the database, or in the database's own internal documents, or separate. In this way, when after more than a year after the back to do the first two versions of the opportunity to make mistakes will be significantly reduced.
6) Testing, testing, repeated testing to establish or amend the database, users must use the new data entered field test data. The most important thing is to allow users to test and to work together to ensure that users choose the same type of data to meet business requirements. Tests need to invest in the new database prior to the completion of the actual services.
7) check the design in the development of the database design during the inspection of the commonly used technology is supported through the application of its prototype to check the database. In other words, for each of the final prototype of the application of expression data to ensure that you check the data model and how to remove the data view.