The following 14 techniques, many people in a large number of database analysis and design practice, summed up gradually. For the use of these experiences, the reader can not help bowls of Health, by rote, and to digest the understanding, seek truth from facts, flexibility. And gradually to do: in the application development, application development.
1. The original documents and the relationship between entities can be one-on-one, one-to-many, many-to-many relationship. Under normal circumstances, they are one-on-one relationship: an original document that corresponds to and corresponds to only one entity. In exceptional circumstances, they may be one-to-many or many-to-one relationship between an original document that corresponds to a number of entities, or more original document corresponding to an entity. The entity can be understood as the basic form. After such a clear correlation between, on the interface we have designed a lot of good input.
〗 〖Example 1: A Biographical information on staff, in terms of human resources information system, it corresponds to the three basic forms: the basic situation of the staff table, form social relationships, work history forms. This is the "original document corresponding to a number of entities," a typical example.
2. The primary key and foreign key in general, an entity can not be no primary key nor foreign key. In E? R chart, the leaves are part of the entity, the primary key can be defined, it may not define the primary key (since it no descendants), but must have a foreign key (because of its father).
Primary key and foreign key design, in the overall database design, occupies an important position. When the overall database design is completed, the United States to have a database design expert said: "The key, there are key, in addition to keys other than nothing", that is, he experiences the database design, but also reflected his information core of the system (data model) of a high degree of abstract thinking. Because: the primary key is a high degree of abstract entities, the primary key and foreign key pair that the connection between the entities.
3. The basic form of the nature of the basic form and the middle table, temporary table, because it has the following four characteristics:
(1) atomicity. The fields in the base table can not be re-decomposition.
(2) primitive. Record of the basic form is the original data (basic data) records.
(3) interpretation of nature. From the basic forms and code data in the table below, can be derived from all of the output data.
(4) stability. The basic structure is relatively stable, the table is to record the long-term preservation.
Understanding of the nature of the basic form, in the design of the database, will be able to base table with the intermediate form, a distinction between temporary table.
4. The basic paradigm of the standard table and the relationship between the field should be to meet the third paradigm. However, to meet the third paradigm of database design, is often not the best design. In order to improve the efficiency of the database is running, and often need to reduce the standard paradigm: the appropriate increase in redundancy, in order to achieve the purpose of space for time.
〗 〖Example 2: There is a basic form of storage of goods, as shown in table 1. "Amount" of the existence of this field indicates that the design of the table are not satisfied with the third paradigm, because "the amount" could be "Price" multiplied by "number" to be, that "amount" is a redundant field. However, the increase "the amount of" field of this redundancy can increase the speed of query statistics, which is the practice space for time.
In Rose 2002, the requirement, there are two types: data and calculations are shown in column. "Amount" This column is called "calculated column" and "Price" and "number" This column is called "data columns."
Table 1 Table commodity trade names of the table structure of the number of commodity price models of the amount of TV 29? 2,500 40 100,000
5. Popular understanding of the three paradigms of popular understanding of the three paradigms, for a lot of good database design. The design of the database, in order to better application of the three paradigms, it is necessary to understand the three paradigm popular (popular enough to understand that understanding is not the most scientific understanding of the most accurate):
The first paradigm: 1NF is the property of the atom bound with the atomic properties of the requirements, can not break down;
The second paradigm: 2NF is the only record of restraint, the only identification requested records, namely, the uniqueness of entities;
The third paradigm: 3NF is bound redundancy field, that is, any field can not be derived from other fields, it requires no redundant field.
There is no redundancy of the database design can be done. However, there is no redundancy of the database may not be the best databases, sometimes in order to enhance operating efficiency, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. Specific approach: the concept of data model in compliance with the design paradigm to reduce the standard paradigm on the design of physical data model to consider. Paradigm is to increase the field reduced to allow redundancy.
6. To be good at to identify and correctly handle the many-to-many relationship
Between the two entities if there is many-to-many relationship, it should be the elimination of such a relationship. Elimination of the method is that an increase in the third between the two entities. In this way, a many-to-many relationship between the original, now turned into one-to-many relationship between the two. To the original properties of the two entities reasonably assigned to one of three entities to. The third entity, in essence, is a more complex relationship, which corresponds to a base table. In general, the database design tool for many-to-many relationship can not be identified, but can handle many-to-many relationship.
〗 〖Example 3: In the "Library Information System", the "book" is an entity, "the reader" is an entity. This relationship between the two entities, is a typical many-to-many relationships: a book can be at different times to borrow more than the reader, a reader can also take advantage of many books. To this end, an increase in the third between the two entities, the entity named "by the book", and its properties as follows: use of time, make use of signs (0 said library, said a book), In addition, it should have two foreign key ( "book" of the primary key, "reader" of the primary key), so that it can with the "Book" and "readers" to connect.
7. The primary key PK is the PK value method for programmers to use the table tool to inter-connect, can be a non-physical meaning of the number of strings, this program will automatically add 1 to achieve. Can also have the physical meaning of the field or a combination of field names. However good the former than the latter. When PK is a combination of field names, the proposed number of fields not too much more than the index not only occupy space, and slow speed.
8. A correct understanding of data redundancy and the foreign key primary key table in a multi-repeat, do not belong to data redundancy, the concept must be clear, in fact, many people still do not know. Non-key fields repeat, is the data redundancy! It is a low-level redundancy, redundant or repetitive. High redundancy is not a repeat field, but derived field appears.
4〗 〖cases: products of the "unit price, quantity, the amount of" three fields, "amount" is from "Price" multiplied by "number of" derived, it is redundant, but also a high redundancy. Redundancy is designed to improve processing speed. Only low-level redundant data inconsistency will increase, because the same data may be from a different time, place, time and again the role of entry. Therefore, we promote high-level redundancy (derivative of redundancy), against the low-level redundant (repetitive redundancy).
9. E - R diagram, there is no standard answer Information System E - R diagram, there is no standard answer, because of its design and painting is not the only, as long as it covers the system requirements and functions of the contents of the scope of business is feasible. In contrast to amend the E - R diagram. Although it was not the only answer, does not mean that the design can be. Good E? R standard map: the structure of clear, simple correlation, the number of moderate entity, a reasonable property distribution, there is no low-level redundancy.
10. View the design of the database technology is useful and basic form, the code table, intermediate table is different from the view is a virtual table, which is dependent on the data source table exists. View is to use a database programmer for a window-based form is a form of data integration is a method of data processing, user data confidentiality is a means. In order to carry out complex processing to improve the computing speed and storage space savings, the general view of the definition of the depth of no more than three. If the three-view is not enough, it should be defined in view of temporary tables, temporary table in the definition of another view. This overlap repeated the definition of the depth view of the unrestricted.
For some with the country's political, economic, technological, military and security interests related to information systems, the role of view, even more important. Basic form of these systems after the completion of physical design, the basic form in the immediate establishment of the first layer view, this view of the number of layers and structures, and the basic form is identical to the number and structure. And provides all the programmers, are to operate only in the view. Only a database administrator, together with a number of staff have "security key" in order to direct operations in the basic form. Reader to think: Why is this?
11. The middle table, temporary table statements and intermediate storage of statistical data table is the table, it is for data storage, or output statements designed to query results, and sometimes it does not have primary keys and foreign keys (except for data warehouse). Temporary table is designed by programmers individuals, temporary storage of records for personal use. Base tables and intermediate forms from DBA maintenance, temporary table used by programmers to their own procedures for self-maintenance.
12. Integrity constraints in the three aspects of the performance of the integrity of the domain: to achieve with Check constraints in the database design tools, the value of the field definition of the scope, there is a Check button, through which the definition of the value of field City. Referential Integrity: The PK, FK, table-level triggers to achieve. The integrity of user-defined: it is a number of business rules with stored procedures and triggers to achieve.
13. To prevent the database design patching method is the "three little principle"
(1) a database table of the number the better. Only a small number of tables in order to explain the system of E - R diagram Fewer but Better remove superfluous duplicate entity, the formation of a highly abstract, objective world, a data integration system to prevent the patching design;
(2) a combination of the primary key table number of the better fields. The role of the primary key because, first, to build the primary key index, and the other is as a sub-table foreign key, the combination of the number of primary key fields of less not only saves the running time and storage space to save the index;
(3) a number of fields in the table the better. Only a small number of fields in order to explain does not exist in the system of data duplication, and little data redundancy and, more importantly, to urge the readers to learn to "change out" and thus to prevent the sub-table of the words paragraph into the main table to table in the main left many vacant fields. The so-called "change out" and is the main part of the table pull out separately to build a sub-table. This method is very simple, some people are not accustomed to, not to adopt, not the implementation.
Practical database design principles are: in the data processing speed between the redundancy and to find a suitable balance. "Three small" concept as a whole, integrated point of view, a principle can not be isolated. The principle is relative, not absolute. "More than three" principle is wrong. Just imagine: If the same cover system function, 100 entities (a total of 1000 properties) of the E - R diagram, certainly more than 200 entities (a total of 2000 properties) of the E - R diagram, it is necessary to better .
The promotion of "three small" principle, is called the design of readers learn to use database technology for data integration systems. Data integration is to document the steps for the application of database systems, application of the database will be the theme of integrated databases, the database will be integrated into the overall theme of a comprehensive database. The higher the degree of integration, data sharing and the stronger, the less information island phenomenon, the entire enterprise information system of the overall E? R chart the number of entities, the number of primary key, the number of attributes will less.
The promotion of "three small" principle purpose is to prevent readers using patching techniques, continuous additions and deletions to the database to enable enterprises to become a free database design database table of "garbage", or the database table "大杂院", and finally resulted in the basic table in the database, code table, intermediate table, temporary table disorganized numerous, leading enterprises and institutions to safeguard the information systems can not be paralyzed.
"More than three" principle that can be done by any person, the principle is the "patch method" sophistry database design theory. "Three small" principle is the principle Fewer but Better, which requires higher skills in database design and art, not anyone can do, because the principle is to eliminate the "patch method" of the theoretical basis for the design of the database.
14. To improve the efficiency of the way the database is running in a given system of hardware and system software conditions, improve the operating efficiency of the database system is to:
(1) in the database physical design, the lower the paradigm to increase the redundancy, the use of triggers, stored procedures using.
(2) When the calculation is very complex and very large number of records (for example, 10000000), the complexity of calculating the first outside the database to the file system approach with C + + language computing is complete, the last to form additional storage去. This is a telecommunications billing system design experience.
(3) a table of records found that too much, for example, more than 10 million, will be the level of the partition table. The level of segmentation is to the table a primary key PK values for the line, record the level of the table divided into two tables. If it is found the field too much of a table, for example, more than 80, the vertical partition of the table, a table of the original broken down into two tables.
(4) of the DBMS database management system for system optimization, that is, optimization of various system parameters, such as the number of the buffer zone.
(5) in the use of data-oriented programming language SQL, the optimization algorithm to take as much as possible.
In short, it is necessary to improve the operating efficiency of the database must be from the database system-level optimization, database design-level optimization, program-level optimization, the three-level efforts at the same time.
1. The original documents and the relationship between entities can be one-on-one, one-to-many, many-to-many relationship. Under normal circumstances, they are one-on-one relationship: an original document that corresponds to and corresponds to only one entity. In exceptional circumstances, they may be one-to-many or many-to-one relationship between an original document that corresponds to a number of entities, or more original document corresponding to an entity. The entity can be understood as the basic form. After such a clear correlation between, on the interface we have designed a lot of good input.
〗 〖Example 1: A Biographical information on staff, in terms of human resources information system, it corresponds to the three basic forms: the basic situation of the staff table, form social relationships, work history forms. This is the "original document corresponding to a number of entities," a typical example.
2. The primary key and foreign key in general, an entity can not be no primary key nor foreign key. In E? R chart, the leaves are part of the entity, the primary key can be defined, it may not define the primary key (since it no descendants), but must have a foreign key (because of its father).
Primary key and foreign key design, in the overall database design, occupies an important position. When the overall database design is completed, the United States to have a database design expert said: "The key, there are key, in addition to keys other than nothing", that is, he experiences the database design, but also reflected his information core of the system (data model) of a high degree of abstract thinking. Because: the primary key is a high degree of abstract entities, the primary key and foreign key pair that the connection between the entities.
3. The basic form of the nature of the basic form and the middle table, temporary table, because it has the following four characteristics:
(1) atomicity. The fields in the base table can not be re-decomposition.
(2) primitive. Record of the basic form is the original data (basic data) records.
(3) interpretation of nature. From the basic forms and code data in the table below, can be derived from all of the output data.
(4) stability. The basic structure is relatively stable, the table is to record the long-term preservation.
Understanding of the nature of the basic form, in the design of the database, will be able to base table with the intermediate form, a distinction between temporary table.
4. The basic paradigm of the standard table and the relationship between the field should be to meet the third paradigm. However, to meet the third paradigm of database design, is often not the best design. In order to improve the efficiency of the database is running, and often need to reduce the standard paradigm: the appropriate increase in redundancy, in order to achieve the purpose of space for time.
〗 〖Example 2: There is a basic form of storage of goods, as shown in table 1. "Amount" of the existence of this field indicates that the design of the table are not satisfied with the third paradigm, because "the amount" could be "Price" multiplied by "number" to be, that "amount" is a redundant field. However, the increase "the amount of" field of this redundancy can increase the speed of query statistics, which is the practice space for time.
In Rose 2002, the requirement, there are two types: data and calculations are shown in column. "Amount" This column is called "calculated column" and "Price" and "number" This column is called "data columns."
Table 1 Table commodity trade names of the table structure of the number of commodity price models of the amount of TV 29? 2,500 40 100,000
5. Popular understanding of the three paradigms of popular understanding of the three paradigms, for a lot of good database design. The design of the database, in order to better application of the three paradigms, it is necessary to understand the three paradigm popular (popular enough to understand that understanding is not the most scientific understanding of the most accurate):
The first paradigm: 1NF is the property of the atom bound with the atomic properties of the requirements, can not break down;
The second paradigm: 2NF is the only record of restraint, the only identification requested records, namely, the uniqueness of entities;
The third paradigm: 3NF is bound redundancy field, that is, any field can not be derived from other fields, it requires no redundant field.
There is no redundancy of the database design can be done. However, there is no redundancy of the database may not be the best databases, sometimes in order to enhance operating efficiency, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. Specific approach: the concept of data model in compliance with the design paradigm to reduce the standard paradigm on the design of physical data model to consider. Paradigm is to increase the field reduced to allow redundancy.
6. To be good at to identify and correctly handle the many-to-many relationship
Between the two entities if there is many-to-many relationship, it should be the elimination of such a relationship. Elimination of the method is that an increase in the third between the two entities. In this way, a many-to-many relationship between the original, now turned into one-to-many relationship between the two. To the original properties of the two entities reasonably assigned to one of three entities to. The third entity, in essence, is a more complex relationship, which corresponds to a base table. In general, the database design tool for many-to-many relationship can not be identified, but can handle many-to-many relationship.
〗 〖Example 3: In the "Library Information System", the "book" is an entity, "the reader" is an entity. This relationship between the two entities, is a typical many-to-many relationships: a book can be at different times to borrow more than the reader, a reader can also take advantage of many books. To this end, an increase in the third between the two entities, the entity named "by the book", and its properties as follows: use of time, make use of signs (0 said library, said a book), In addition, it should have two foreign key ( "book" of the primary key, "reader" of the primary key), so that it can with the "Book" and "readers" to connect.
7. The primary key PK is the PK value method for programmers to use the table tool to inter-connect, can be a non-physical meaning of the number of strings, this program will automatically add 1 to achieve. Can also have the physical meaning of the field or a combination of field names. However good the former than the latter. When PK is a combination of field names, the proposed number of fields not too much more than the index not only occupy space, and slow speed.
8. A correct understanding of data redundancy and the foreign key primary key table in a multi-repeat, do not belong to data redundancy, the concept must be clear, in fact, many people still do not know. Non-key fields repeat, is the data redundancy! It is a low-level redundancy, redundant or repetitive. High redundancy is not a repeat field, but derived field appears.
4〗 〖cases: products of the "unit price, quantity, the amount of" three fields, "amount" is from "Price" multiplied by "number of" derived, it is redundant, but also a high redundancy. Redundancy is designed to improve processing speed. Only low-level redundant data inconsistency will increase, because the same data may be from a different time, place, time and again the role of entry. Therefore, we promote high-level redundancy (derivative of redundancy), against the low-level redundant (repetitive redundancy).
9. E - R diagram, there is no standard answer Information System E - R diagram, there is no standard answer, because of its design and painting is not the only, as long as it covers the system requirements and functions of the contents of the scope of business is feasible. In contrast to amend the E - R diagram. Although it was not the only answer, does not mean that the design can be. Good E? R standard map: the structure of clear, simple correlation, the number of moderate entity, a reasonable property distribution, there is no low-level redundancy.
10. View the design of the database technology is useful and basic form, the code table, intermediate table is different from the view is a virtual table, which is dependent on the data source table exists. View is to use a database programmer for a window-based form is a form of data integration is a method of data processing, user data confidentiality is a means. In order to carry out complex processing to improve the computing speed and storage space savings, the general view of the definition of the depth of no more than three. If the three-view is not enough, it should be defined in view of temporary tables, temporary table in the definition of another view. This overlap repeated the definition of the depth view of the unrestricted.
For some with the country's political, economic, technological, military and security interests related to information systems, the role of view, even more important. Basic form of these systems after the completion of physical design, the basic form in the immediate establishment of the first layer view, this view of the number of layers and structures, and the basic form is identical to the number and structure. And provides all the programmers, are to operate only in the view. Only a database administrator, together with a number of staff have "security key" in order to direct operations in the basic form. Reader to think: Why is this?
11. The middle table, temporary table statements and intermediate storage of statistical data table is the table, it is for data storage, or output statements designed to query results, and sometimes it does not have primary keys and foreign keys (except for data warehouse). Temporary table is designed by programmers individuals, temporary storage of records for personal use. Base tables and intermediate forms from DBA maintenance, temporary table used by programmers to their own procedures for self-maintenance.
12. Integrity constraints in the three aspects of the performance of the integrity of the domain: to achieve with Check constraints in the database design tools, the value of the field definition of the scope, there is a Check button, through which the definition of the value of field City. Referential Integrity: The PK, FK, table-level triggers to achieve. The integrity of user-defined: it is a number of business rules with stored procedures and triggers to achieve.
13. To prevent the database design patching method is the "three little principle"
(1) a database table of the number the better. Only a small number of tables in order to explain the system of E - R diagram Fewer but Better remove superfluous duplicate entity, the formation of a highly abstract, objective world, a data integration system to prevent the patching design;
(2) a combination of the primary key table number of the better fields. The role of the primary key because, first, to build the primary key index, and the other is as a sub-table foreign key, the combination of the number of primary key fields of less not only saves the running time and storage space to save the index;
(3) a number of fields in the table the better. Only a small number of fields in order to explain does not exist in the system of data duplication, and little data redundancy and, more importantly, to urge the readers to learn to "change out" and thus to prevent the sub-table of the words paragraph into the main table to table in the main left many vacant fields. The so-called "change out" and is the main part of the table pull out separately to build a sub-table. This method is very simple, some people are not accustomed to, not to adopt, not the implementation.
Practical database design principles are: in the data processing speed between the redundancy and to find a suitable balance. "Three small" concept as a whole, integrated point of view, a principle can not be isolated. The principle is relative, not absolute. "More than three" principle is wrong. Just imagine: If the same cover system function, 100 entities (a total of 1000 properties) of the E - R diagram, certainly more than 200 entities (a total of 2000 properties) of the E - R diagram, it is necessary to better .
The promotion of "three small" principle, is called the design of readers learn to use database technology for data integration systems. Data integration is to document the steps for the application of database systems, application of the database will be the theme of integrated databases, the database will be integrated into the overall theme of a comprehensive database. The higher the degree of integration, data sharing and the stronger, the less information island phenomenon, the entire enterprise information system of the overall E? R chart the number of entities, the number of primary key, the number of attributes will less.
The promotion of "three small" principle purpose is to prevent readers using patching techniques, continuous additions and deletions to the database to enable enterprises to become a free database design database table of "garbage", or the database table "大杂院", and finally resulted in the basic table in the database, code table, intermediate table, temporary table disorganized numerous, leading enterprises and institutions to safeguard the information systems can not be paralyzed.
"More than three" principle that can be done by any person, the principle is the "patch method" sophistry database design theory. "Three small" principle is the principle Fewer but Better, which requires higher skills in database design and art, not anyone can do, because the principle is to eliminate the "patch method" of the theoretical basis for the design of the database.
14. To improve the efficiency of the way the database is running in a given system of hardware and system software conditions, improve the operating efficiency of the database system is to:
(1) in the database physical design, the lower the paradigm to increase the redundancy, the use of triggers, stored procedures using.
(2) When the calculation is very complex and very large number of records (for example, 10000000), the complexity of calculating the first outside the database to the file system approach with C + + language computing is complete, the last to form additional storage去. This is a telecommunications billing system design experience.
(3) a table of records found that too much, for example, more than 10 million, will be the level of the partition table. The level of segmentation is to the table a primary key PK values for the line, record the level of the table divided into two tables. If it is found the field too much of a table, for example, more than 80, the vertical partition of the table, a table of the original broken down into two tables.
(4) of the DBMS database management system for system optimization, that is, optimization of various system parameters, such as the number of the buffer zone.
(5) in the use of data-oriented programming language SQL, the optimization algorithm to take as much as possible.
In short, it is necessary to improve the operating efficiency of the database must be from the database system-level optimization, database design-level optimization, program-level optimization, the three-level efforts at the same time.