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: the one and only original documents corresponding to the corresponding one entity. In exceptional circumstances, they probably are 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. Entity here can be understood as the basic form. A clear correlation between this after our entry interface design a lot of good. 〖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 more than one entity," a typical example.
2. The primary key and foreign key in general, an entity should not neither primary key nor foreign key. At E-R diagram, in the leaf parts of entities, can define the primary key, it may not define the primary key (because 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, apart from outside the key, nothing", that is, database design his experiences, but also reflected his information core of the system (data model) of a high degree of abstract thought. Since: primary key entities are highly abstract, the primary key and foreign key pair, said the connection between the entities.
3. The basic nature of the table between the basic forms and tables, temporary table is different 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 raw data (basic data) records.
(3) deductive 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 records are long-term preservation.
Understanding of the nature of the basic table, in the design of the database, will be able to base table and between tables, a distinction between temporary table.
4. Paradigm standard
Base table and its field relations, 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, usually 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 table storage of merchandise, such as shown in table 1. "Amount" in this field exist, indicating that the design of the table are not satisfied with the third paradigm, because "the amount" can be by the "unit price" multiplied by the "quantity" to be, that "amount" fields are redundant. However, the increase in "amount" of the redundant field, can improve the speed of query statistics, which is based on space-for-time approach. At Rose 2002, the requirement has two types: data columns and the calculations are shown. "Amount" This column is called "calculated column" and "unit price" and "quantity" This column is called "data columns."
Table 1 merchandise table table structure
Model name merchandise merchandise Quantity Unit Price Amount
TV 29-inch 2,500 40 100,000
5. Popular understanding of the three paradigms
Popular understanding of the three paradigm for database design a lot of good. 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 to require property with atoms, and can not decompose;
The second paradigm: 2NF Record is the only constraint, there is only on record labels, namely, the uniqueness of entities;
The third paradigm: 3NF is bound field redundancy, 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, and sometimes in order to improve operating efficiency, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. The specific approach are: the concept of data model at the design comply with the third paradigm, the paradigm to reduce the standard work on the physical data model design to consider. Reduce the paradigm is to increase the field, permit redundancy.
6. To be good at to identify and correctly handle the many-to-many relationship
If the two entities many-to-many relationship between the existence should be the elimination of such a relationship. The elimination method is that between a third entity. In this way, the original of a many-to-many relationship, and now turned into two one-to-many relationship. Two entities to the original property is reasonably assigned to one of three entities go. Here is a third entity, in essence, are 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 at different times can be a number of readers to borrow, a reader can borrow many books. Therefore, it is necessary to increase at the third between the two entities, the entity named "book also borrow" its property as follows: use of time, make use of signs (0 express library, also express one book), In addition, it should have two foreign keys ( "book" of the primary key, "reader" of the primary key), so that it can with the "Book" and "reader" to connect.
7. The primary key PK values Ways
PK programmers are used for inter-connecting instrument table, it can be one without the physical meaning of the numeral strings, this program will automatically add one to achieve. Can also have physical meaning are the field names or field names combination. But the former than the latter good. 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
Primary key and foreign key in the table at many recurring, do not belong to data redundancy, the concept must be clear that in fact there is lot of people remains unclear. Non-key field repeated is the data redundancy! And are a low-level redundancy, that is, repetitive redundancy. Advanced redundancy is not repeated fields, but fields appear derivative.
Example 4〗 〖: merchandise in the "unit price, quantity, the amount of the" three fields, "amount" is from "Unit Price" multiplied by the "quantity" derived, it is redundant, and is an advanced redundant. Redundancy is designed to improve processing speed. Only low-level redundant data inconsistency will only increase, because the same data, probably from a different time, place, role entry on several occasions. Therefore, the promotion of our advanced redundant (derivative of redundancy), opposed 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 scope of business system requirements and functions of the content, that is feasible. In contrast to amend E - R diagram. Even though it has not only the standard answer, does not mean that can be designed.
GOOD E-R diagram of the standard are: the structure of clear, concise association, entity number of moderate and reasonable property distribution, there is no low-level redundancy.
10. View technology in the database design is useful
And basic tables, code tables, table between different views is a virtual table, which is dependent on the data source tables exist. View are available for programmers to use the database a window-based forms are a form of data integration is a method of data processing, user data confidentiality are a means. In order to deal with complex, enhance computational speed and save storage space, view the definition of the depth of the general should not exceed three. If the three-view is still not enough, it should be on view at the definition of temporary table, in the temporary table and then on the definition of view. This repeated the definition of overlap, the depth of view on 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. These systems are essential to complete physical design table immediately after the basic table set up on the first level view, this view of the number of layers and structure, and basic table number and structure are identical. And provides that all programmers, are on view only at the operation. Only a database administrator, with a number of staff know what the "security key" in order to direct the operation at the basic form. Please think about the reader: This is Why?
11. Between tables, and the Provisional Report Form
Between tables of statistical data are stored in table, 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 table are individual programmers designed temporary storage of records, for personal use. Base table and between tables by the DBA maintenance, temporary table used by the programmers themselves automatically safeguard.
12. Integrity constraints at three aspects of performance
Domain Integrity: Check to use implementation constraints in the database design tools, the range of fields are defined when there is a Check button, through its definition of the value of the city field.
Referential Integrity: The PK, FK, table-level triggers to achieve.
User-defined integrity: it is a number of business rules, using stored procedures and triggers to achieve.
13. Designed to prevent the database patching method is "less three principles"
(1) a database table of the number the better. Only a small number of tables, can we explain the system E - R diagram Fewer but Better remove superfluous duplicate entities, the formation of the highly abstract, objective world, a system of data integration, to prevent the patching design;
(2) a table primary key combination of the number of the better fields. Since the role of the primary key First, 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 indexed to save storage space;
(3) a number of fields in the table the better. Only a less 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 prevented the sub-table of the word paragraph into the main table to go, in the main table, left vacant much of the field.
The so-called "change out" and is the main part of the table pulling 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 less" is a holistic concept, General opinion, should not isolate a certain principle. The principle is relative, not absolute. "Sanduo" 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 .
To promote the "three small" principle, are called readers learn to use technology for database design system data integration. Data integration step is to file system for the application of integrated database, the database will be applied for the subject integrated databases, database integration will be subject to the overall integrated 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 diagram of the number of entities, the number of primary key, the number of property will be less.
To promote the "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 database table of "大杂院" Finally resulted in the basic table in the database, code table, middle table, temporary table disorganized and numerous, leading enterprises and institutions to safeguard the information system can not paralyzed.
"Sanduo" principle that can be done by any person, the principle is "patching method" specious argument that doctrine of the design database. "Three less" principle is the principle Fewer but Better, which requires a higher degree of database design skills and artistic, not anyone can do, because the principle is to eliminate the "patch method" database design theory.
14. Improve operating efficiency database approach
At a given system hardware and system software under the conditions of the operation of database systems enhance the efficiency of the approach are:
(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 using 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) found that the records of a table too much, such as 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 records the level of segmentation for the two tables. If it is found too much of a table field, such as more than 80, then the vertical partition of the table, the original table is broken down into two tables.
(4) database management system DBMS for system optimization, namely, optimization of various system parameters, such as the number of the buffer zone.
(5) in the use of SQL data-oriented programming language, the optimization algorithm to take as much as possible.
In short, it is necessary to improve the operational efficiency of the database must be from the database system-level optimization, database design-level optimization, procedural implementation-level optimization, it simultaneously on three levels of efforts.
These 14 skills 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 achieve: in the application development, application development.
Source article: http://www.diybl.com/course/7_databases/oracle/Oracleshl/200865/121744.html
2. The primary key and foreign key in general, an entity should not neither primary key nor foreign key. At E-R diagram, in the leaf parts of entities, can define the primary key, it may not define the primary key (because 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, apart from outside the key, nothing", that is, database design his experiences, but also reflected his information core of the system (data model) of a high degree of abstract thought. Since: primary key entities are highly abstract, the primary key and foreign key pair, said the connection between the entities.
3. The basic nature of the table between the basic forms and tables, temporary table is different 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 raw data (basic data) records.
(3) deductive 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 records are long-term preservation.
Understanding of the nature of the basic table, in the design of the database, will be able to base table and between tables, a distinction between temporary table.
4. Paradigm standard
Base table and its field relations, 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, usually 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 table storage of merchandise, such as shown in table 1. "Amount" in this field exist, indicating that the design of the table are not satisfied with the third paradigm, because "the amount" can be by the "unit price" multiplied by the "quantity" to be, that "amount" fields are redundant. However, the increase in "amount" of the redundant field, can improve the speed of query statistics, which is based on space-for-time approach. At Rose 2002, the requirement has two types: data columns and the calculations are shown. "Amount" This column is called "calculated column" and "unit price" and "quantity" This column is called "data columns."
Table 1 merchandise table table structure
Model name merchandise merchandise Quantity Unit Price Amount
TV 29-inch 2,500 40 100,000
5. Popular understanding of the three paradigms
Popular understanding of the three paradigm for database design a lot of good. 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 to require property with atoms, and can not decompose;
The second paradigm: 2NF Record is the only constraint, there is only on record labels, namely, the uniqueness of entities;
The third paradigm: 3NF is bound field redundancy, 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, and sometimes in order to improve operating efficiency, it is necessary to reduce the standard paradigm, appropriate to retain redundant data. The specific approach are: the concept of data model at the design comply with the third paradigm, the paradigm to reduce the standard work on the physical data model design to consider. Reduce the paradigm is to increase the field, permit redundancy.
6. To be good at to identify and correctly handle the many-to-many relationship
If the two entities many-to-many relationship between the existence should be the elimination of such a relationship. The elimination method is that between a third entity. In this way, the original of a many-to-many relationship, and now turned into two one-to-many relationship. Two entities to the original property is reasonably assigned to one of three entities go. Here is a third entity, in essence, are 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 at different times can be a number of readers to borrow, a reader can borrow many books. Therefore, it is necessary to increase at the third between the two entities, the entity named "book also borrow" its property as follows: use of time, make use of signs (0 express library, also express one book), In addition, it should have two foreign keys ( "book" of the primary key, "reader" of the primary key), so that it can with the "Book" and "reader" to connect.
7. The primary key PK values Ways
PK programmers are used for inter-connecting instrument table, it can be one without the physical meaning of the numeral strings, this program will automatically add one to achieve. Can also have physical meaning are the field names or field names combination. But the former than the latter good. 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
Primary key and foreign key in the table at many recurring, do not belong to data redundancy, the concept must be clear that in fact there is lot of people remains unclear. Non-key field repeated is the data redundancy! And are a low-level redundancy, that is, repetitive redundancy. Advanced redundancy is not repeated fields, but fields appear derivative.
Example 4〗 〖: merchandise in the "unit price, quantity, the amount of the" three fields, "amount" is from "Unit Price" multiplied by the "quantity" derived, it is redundant, and is an advanced redundant. Redundancy is designed to improve processing speed. Only low-level redundant data inconsistency will only increase, because the same data, probably from a different time, place, role entry on several occasions. Therefore, the promotion of our advanced redundant (derivative of redundancy), opposed 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 scope of business system requirements and functions of the content, that is feasible. In contrast to amend E - R diagram. Even though it has not only the standard answer, does not mean that can be designed.
GOOD E-R diagram of the standard are: the structure of clear, concise association, entity number of moderate and reasonable property distribution, there is no low-level redundancy.
10. View technology in the database design is useful
And basic tables, code tables, table between different views is a virtual table, which is dependent on the data source tables exist. View are available for programmers to use the database a window-based forms are a form of data integration is a method of data processing, user data confidentiality are a means. In order to deal with complex, enhance computational speed and save storage space, view the definition of the depth of the general should not exceed three. If the three-view is still not enough, it should be on view at the definition of temporary table, in the temporary table and then on the definition of view. This repeated the definition of overlap, the depth of view on 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. These systems are essential to complete physical design table immediately after the basic table set up on the first level view, this view of the number of layers and structure, and basic table number and structure are identical. And provides that all programmers, are on view only at the operation. Only a database administrator, with a number of staff know what the "security key" in order to direct the operation at the basic form. Please think about the reader: This is Why?
11. Between tables, and the Provisional Report Form
Between tables of statistical data are stored in table, 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 table are individual programmers designed temporary storage of records, for personal use. Base table and between tables by the DBA maintenance, temporary table used by the programmers themselves automatically safeguard.
12. Integrity constraints at three aspects of performance
Domain Integrity: Check to use implementation constraints in the database design tools, the range of fields are defined when there is a Check button, through its definition of the value of the city field.
Referential Integrity: The PK, FK, table-level triggers to achieve.
User-defined integrity: it is a number of business rules, using stored procedures and triggers to achieve.
13. Designed to prevent the database patching method is "less three principles"
(1) a database table of the number the better. Only a small number of tables, can we explain the system E - R diagram Fewer but Better remove superfluous duplicate entities, the formation of the highly abstract, objective world, a system of data integration, to prevent the patching design;
(2) a table primary key combination of the number of the better fields. Since the role of the primary key First, 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 indexed to save storage space;
(3) a number of fields in the table the better. Only a less 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 prevented the sub-table of the word paragraph into the main table to go, in the main table, left vacant much of the field.
The so-called "change out" and is the main part of the table pulling 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 less" is a holistic concept, General opinion, should not isolate a certain principle. The principle is relative, not absolute. "Sanduo" 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 .
To promote the "three small" principle, are called readers learn to use technology for database design system data integration. Data integration step is to file system for the application of integrated database, the database will be applied for the subject integrated databases, database integration will be subject to the overall integrated 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 diagram of the number of entities, the number of primary key, the number of property will be less.
To promote the "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 database table of "大杂院" Finally resulted in the basic table in the database, code table, middle table, temporary table disorganized and numerous, leading enterprises and institutions to safeguard the information system can not paralyzed.
"Sanduo" principle that can be done by any person, the principle is "patching method" specious argument that doctrine of the design database. "Three less" principle is the principle Fewer but Better, which requires a higher degree of database design skills and artistic, not anyone can do, because the principle is to eliminate the "patch method" database design theory.
14. Improve operating efficiency database approach
At a given system hardware and system software under the conditions of the operation of database systems enhance the efficiency of the approach are:
(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 using 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) found that the records of a table too much, such as 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 records the level of segmentation for the two tables. If it is found too much of a table field, such as more than 80, then the vertical partition of the table, the original table is broken down into two tables.
(4) database management system DBMS for system optimization, namely, optimization of various system parameters, such as the number of the buffer zone.
(5) in the use of SQL data-oriented programming language, the optimization algorithm to take as much as possible.
In short, it is necessary to improve the operational efficiency of the database must be from the database system-level optimization, database design-level optimization, procedural implementation-level optimization, it simultaneously on three levels of efforts.
These 14 skills 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 achieve: in the application development, application development.
Source article: http://www.diybl.com/course/7_databases/oracle/Oracleshl/200865/121744.html