The following 14 techniques are lot of people at many 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. 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 an entity only. In exceptional circumstances, they are probably 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.
〖1〗 Example: 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, job resume form. 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 should not 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 it has the 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 there is a database design expert said: "The key, there are key, in addition to key outside, nothing", that is, he experiences the database design, and reflect the information he core of the system (data model) of a high degree of abstract thought. Since: 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 nature of the table between the basic forms and tables, temporary table, because it has the following four characteristics:
(1) atomicity. The basic fields in the table are no longer decomposition.
(2) of original. Record the basic table are 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 records of the table are the long-term preservation.
Understanding of the nature of the basic form, in the design of the database, will be able to form between the basic form, a distinction between temporary table.
4. The basic paradigm of the standard table and the relationship between the field and should try to meet the third paradigm. However, to meet the third paradigm of database design, is often not the best design. Database in order to improve operating efficiency, is often necessary to reduce the standard paradigm: the appropriate increase in redundancy, in order to achieve the purpose of space for time.
2〗 〖Example: there is a basic form of the merchandise store, 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" can be from "unit price" multiplied by the "quantity" to be, that "amount" fields are redundant. However, the increase "the amount of" field of this redundancy, you can improve the speed of query statistics, which is the practice space for time.
At Rose 2002, the requirement has two types: data and calculations are shown in column. "Amount" This column is called "calculated column" and "unit price" and "quantity" This column is called "data columns."
Table of Tables Table 1 Structure of merchandise merchandise merchandise Model Name Quantity Unit Price Amount TV 29? 250,040,100,000
5. Popular understanding of the three paradigms of popular understanding of the three paradigms, for a lot of good database design. In the database design, in order to better application of the three paradigms, it is necessary to understand the three paradigm popular (popular enough to understand the understanding are not the most scientific understanding of the most accurate):
The first paradigm: 1NF is the property of the atoms bound by the requirements of property with the atoms, can not break down;
The second paradigm: 2NF Record is the only constraint, there is only on record labels, that is, 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 database in order to improve operational efficiency in some cases, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. Specific practices are: the concept of data model in the design comply with the third paradigm, the paradigm to reduce the standard work on the physical data model design to consider. Paradigm is to increase the field reduced to allow redundancy.
6. Right to be good at to identify and deal with many-to-many relationship
Exist between the two entities if the 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. Two entities to the original property is reasonably assigned to one of three entities go. 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 borrow many books. Therefore, it is necessary to increase at the third between the two entities, the entity known as "book also borrow", and its property as follows: use of time, make use of signs (0 express library, one book that also), 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 value of the primary key PK Ways
PK programmers are used for inter-connecting instrument table, it can be one figure without physical meaning of the string, add one by the process to achieve automatic. Can also have physical meaning are the field names or a combination of field names. But the former better than the latter. When PK is a combination of field names, it is recommended not to too much the number of fields, many of the index space is not only big, but slow speed.
8. A correct understanding of data redundancy and the foreign key primary key in the table at many repeat, do not belong to data redundancy, the concept must be clear that in fact there is lot of people remains unclear. Non-key fields repeat, is the data redundancy! And is a low-level redundancy, redundant or repetitive. Advanced redundancy is not repeated fields, but fields derived there.
Example 4〗 〖: merchandise in the "unit price, quantity, the amount of" three fields, "amount" is from "unit price" multiplied by the "quantity" 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, probably from a different time, place and role of entry on several occasions. Therefore, we advocate advanced redundancy (derivative of redundancy), object to 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 it is designed with the law is not the only draw, 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. Even though it was not the only answer, does not mean that the design can be. GOOD E? R standard map are: 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, table between different view is a virtual table, which is dependent on the data source tables exist. View is to use a database programmer for a window-based forms are a form of data integration is a method of data processing, user data are a means of Security. Complex in order to deal with 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 on view at the definition of a temporary table, in the temporary table and then on the definition of 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 of the information system, the role of view, even more important. Basic form of these systems after the completion of physical design, the basic form immediately at the first level set up on the view that this view of the number of layers and structures, and the basic form and structure are identical to the number. And provides all the programmers, will be on view only at the operation. Only a database administrator, together with a number of staff have "security key" in order to directly operate on the basic table. Please think about the reader: This is Why?
11. Between tables, temporary table statements and tables are placed between the tables of statistical data, it is for the data warehouse, Report or query results output designed, and sometimes it does not have primary keys and foreign keys (except for data warehouse). Temporary tables are designed by individual programmers, temporary storage of records for personal use. Between the base table by table and DBA maintenance, temporary table used by the programmers themselves automatically to maintain.
12. Integrity constraints at 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. Designed to prevent the database patching method is "the principle of three small"
(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 are two sub-tables as foreign keys, primary key combinations so the number of small fields, and 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 the system does not exist in duplicate data, and there is very 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 go, leaving the table in the main lot of spare 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: treatment of data redundancy and speed to find the right balance between the points. "Three less" concept as a whole, the Composite opinion, a principle should not isolate. 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 property) of the E - R diagram, certainly more than 200 entities (a total of 2000 property) of the E - R diagram, it is necessary to better .
The promotion of "three small" principle, are called readers learn to use technology for database design data integration system. Data integration is to document the steps for the application of database systems, database integration will be applied subject database, 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 an island situation, the entire enterprise information system of the overall E? R chart the number of entities, the number of primary key, the number of property will be less.
The promotion of "three small" principle purpose is to prevent readers using patching techniques, continuous additions and deletions to the database, so that enterprises can design a database into a database table of "garbage", or the database table "大杂院" Finally resulted in the basic table in the database, code table, between tables, temporary tables and disorderly, and numerous, resulting in enterprises and institutions to safeguard the information system can not paralyzed.
"More than three" principle that can be done by any person, the principle is the "patch method" sophistry database design theory. "Three less" principle is the principle Fewer but Better, which requires higher skills in database design and art, not anyone can do is put an end because of the principle of "patching method" of the theoretical basis for the design of the database.
14. Improve the efficiency of the way the database is running at a given system hardware and system software conditions, improve the efficiency of database systems is to:
(1) in the database physical design, the lower the paradigm to increase the redundancy, the use of triggers, stored procedures with many.
(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 calculated after the completion of treatment, and finally the additional storage to the table去. This is the design of telecommunications billing system experience.
(3) Record found that too much of a table, 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 of the table divided into two levels of Record form. 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, or 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 class optimization, program-level optimization, it simultaneously on three levels of efforts.
1. 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 an entity only. In exceptional circumstances, they are probably 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.
〖1〗 Example: 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, job resume form. 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 should not 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 it has the 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 there is a database design expert said: "The key, there are key, in addition to key outside, nothing", that is, he experiences the database design, and reflect the information he core of the system (data model) of a high degree of abstract thought. Since: 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 nature of the table between the basic forms and tables, temporary table, because it has the following four characteristics:
(1) atomicity. The basic fields in the table are no longer decomposition.
(2) of original. Record the basic table are 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 records of the table are the long-term preservation.
Understanding of the nature of the basic form, in the design of the database, will be able to form between the basic form, a distinction between temporary table.
4. The basic paradigm of the standard table and the relationship between the field and should try to meet the third paradigm. However, to meet the third paradigm of database design, is often not the best design. Database in order to improve operating efficiency, is often necessary to reduce the standard paradigm: the appropriate increase in redundancy, in order to achieve the purpose of space for time.
2〗 〖Example: there is a basic form of the merchandise store, 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" can be from "unit price" multiplied by the "quantity" to be, that "amount" fields are redundant. However, the increase "the amount of" field of this redundancy, you can improve the speed of query statistics, which is the practice space for time.
At Rose 2002, the requirement has two types: data and calculations are shown in column. "Amount" This column is called "calculated column" and "unit price" and "quantity" This column is called "data columns."
Table of Tables Table 1 Structure of merchandise merchandise merchandise Model Name Quantity Unit Price Amount TV 29? 250,040,100,000
5. Popular understanding of the three paradigms of popular understanding of the three paradigms, for a lot of good database design. In the database design, in order to better application of the three paradigms, it is necessary to understand the three paradigm popular (popular enough to understand the understanding are not the most scientific understanding of the most accurate):
The first paradigm: 1NF is the property of the atoms bound by the requirements of property with the atoms, can not break down;
The second paradigm: 2NF Record is the only constraint, there is only on record labels, that is, 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 database in order to improve operational efficiency in some cases, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. Specific practices are: the concept of data model in the design comply with the third paradigm, the paradigm to reduce the standard work on the physical data model design to consider. Paradigm is to increase the field reduced to allow redundancy.
6. Right to be good at to identify and deal with many-to-many relationship
Exist between the two entities if the 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. Two entities to the original property is reasonably assigned to one of three entities go. 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 borrow many books. Therefore, it is necessary to increase at the third between the two entities, the entity known as "book also borrow", and its property as follows: use of time, make use of signs (0 express library, one book that also), 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 value of the primary key PK Ways
PK programmers are used for inter-connecting instrument table, it can be one figure without physical meaning of the string, add one by the process to achieve automatic. Can also have physical meaning are the field names or a combination of field names. But the former better than the latter. When PK is a combination of field names, it is recommended not to too much the number of fields, many of the index space is not only big, but slow speed.
8. A correct understanding of data redundancy and the foreign key primary key in the table at many repeat, do not belong to data redundancy, the concept must be clear that in fact there is lot of people remains unclear. Non-key fields repeat, is the data redundancy! And is a low-level redundancy, redundant or repetitive. Advanced redundancy is not repeated fields, but fields derived there.
Example 4〗 〖: merchandise in the "unit price, quantity, the amount of" three fields, "amount" is from "unit price" multiplied by the "quantity" 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, probably from a different time, place and role of entry on several occasions. Therefore, we advocate advanced redundancy (derivative of redundancy), object to 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 it is designed with the law is not the only draw, 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. Even though it was not the only answer, does not mean that the design can be. GOOD E? R standard map are: 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, table between different view is a virtual table, which is dependent on the data source tables exist. View is to use a database programmer for a window-based forms are a form of data integration is a method of data processing, user data are a means of Security. Complex in order to deal with 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 on view at the definition of a temporary table, in the temporary table and then on the definition of 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 of the information system, the role of view, even more important. Basic form of these systems after the completion of physical design, the basic form immediately at the first level set up on the view that this view of the number of layers and structures, and the basic form and structure are identical to the number. And provides all the programmers, will be on view only at the operation. Only a database administrator, together with a number of staff have "security key" in order to directly operate on the basic table. Please think about the reader: This is Why?
11. Between tables, temporary table statements and tables are placed between the tables of statistical data, it is for the data warehouse, Report or query results output designed, and sometimes it does not have primary keys and foreign keys (except for data warehouse). Temporary tables are designed by individual programmers, temporary storage of records for personal use. Between the base table by table and DBA maintenance, temporary table used by the programmers themselves automatically to maintain.
12. Integrity constraints at 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. Designed to prevent the database patching method is "the principle of three small"
(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 are two sub-tables as foreign keys, primary key combinations so the number of small fields, and 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 the system does not exist in duplicate data, and there is very 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 go, leaving the table in the main lot of spare 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: treatment of data redundancy and speed to find the right balance between the points. "Three less" concept as a whole, the Composite opinion, a principle should not isolate. 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 property) of the E - R diagram, certainly more than 200 entities (a total of 2000 property) of the E - R diagram, it is necessary to better .
The promotion of "three small" principle, are called readers learn to use technology for database design data integration system. Data integration is to document the steps for the application of database systems, database integration will be applied subject database, 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 an island situation, the entire enterprise information system of the overall E? R chart the number of entities, the number of primary key, the number of property will be less.
The promotion of "three small" principle purpose is to prevent readers using patching techniques, continuous additions and deletions to the database, so that enterprises can design a database into a database table of "garbage", or the database table "大杂院" Finally resulted in the basic table in the database, code table, between tables, temporary tables and disorderly, and numerous, resulting in enterprises and institutions to safeguard the information system can not paralyzed.
"More than three" principle that can be done by any person, the principle is the "patch method" sophistry database design theory. "Three less" principle is the principle Fewer but Better, which requires higher skills in database design and art, not anyone can do is put an end because of the principle of "patching method" of the theoretical basis for the design of the database.
14. Improve the efficiency of the way the database is running at a given system hardware and system software conditions, improve the efficiency of database systems is to:
(1) in the database physical design, the lower the paradigm to increase the redundancy, the use of triggers, stored procedures with many.
(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 calculated after the completion of treatment, and finally the additional storage to the table去. This is the design of telecommunications billing system experience.
(3) Record found that too much of a table, 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 of the table divided into two levels of Record form. 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, or 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 class optimization, program-level optimization, it simultaneously on three levels of efforts.