Database Design (RPM)

a popular understanding of the three paradigms is of great benefit to database design. In the database design, in order to better apply the three paradigms, we must understand the three paradigms in a popular way(Popular understanding is sufficient understanding, not the most scientific and accurate understanding):
First Paradigm:1NFis the atomic constraint on the attribute, which requires that the attribute be atomic and non-decomposed.

Second paradigm:2NFIs the uniqueness of the records, requiring that the records have a unique identity, that is, the uniqueness of the entity;

The third paradigm:3NFis a constraint on the redundancy of the field, that is, any field cannot be derived from another field, it requires no redundancy in the field.

There is no redundant database design to do. However, a database without redundancy is not necessarily the best database, and sometimes in order to improve operational efficiency, it is necessary to lower the paradigm standard and properly retain redundant data. The practice is to adhere to the third paradigm when designing the conceptual data model, and to lower the standard of normalization into the design of the physical data model. Lowering the paradigm is adding fields, allowing redundancy.

be good at identifying and correctly dealing with many-to-many relationships

This relationship should be eliminated if there is a many-to-many relationship between the two entities. The solution is to add a third entity between the two. In this way, the original a many-to-many relationship, now becomes two one-to-many relationship. To properly assign the original two entity's attributes to three entities. The third entity here is essentially a more complex relationship that corresponds to a basic table. Generally speaking, the database design tool does not recognize many-to-many relationships, but it can handle many-to-many relationships.

Example: in"Library Information System"in which"Books"is an entity,"Readers"is also an entity. The relationship between these two entities is a typical many-to-many relationship: A book can be borrowed by multiple readers at different times, and a reader can borrow more books. To do this, add a third entity between the two, which is named"Borrow the book", its properties are: Borrow time, borrow also sign(0means borrowing books,1 to return a book), in addition, it should have two foreign keys("Books"the primary key,"Readers"the primary key), so that it can work with"Books"and the"Readers"connection.

correct understanding of data redundancy

duplicate occurrences of primary key and foreign key in multiple tables, is not data redundancy, the concept must be clear, in fact many people are not clear. Duplicate occurrences of non-key fields, is data redundancy! And is a kind of low-level redundancy, that is, repetitive redundancy. Advanced redundancy is not a recurring occurrence of a field, but a derivation of a field.

Example: in a product"unit price, quantity, amount"three fields,"Amount"is the"Unit Price"Multiply"Quantity"derived, it is redundant, and is an advanced redundancy. The purpose of redundancy is to improve processing speed. Only low-level redundancy increases the inconsistency of the data, because the same data can be entered multiple times, from different times, places, and roles. Therefore, we advocate advanced redundancy(Derived Redundancy), against low-level redundancy(Repetitive Redundancy).

E-RFigure no standard answer

of Information SystemsE-RThe figure has no standard answer, because its design and drawing is not unique, as long as it covers the system requirements of the business scope and functional content, is feasible. Conversely, to modifyE-Rfigure. Although it does not have the only standard answer, it does not mean that it can be arbitrarily designed. Yes, okay.E-The standard of the graph is: The structure is clear, the association is concise, the number of entities is moderate, the attribute allocation is reasonable, no low level redundancy.

View technology is useful in database design

Unlike basic tables, code tables, and intermediate tables, a view is a virtual table that relies on a real table of data sources. A view is a window for programmers to use a database, a form of synthesis of base-table data, is a method of data processing, is a means of privacy of users. For complex processing, increased computational speed and storage space savings, the definition depth of a view must not exceed three levels. If the three layer view is still not enough, you should define a temporary table on the view, redefine the view on the staging table. This repeats the overlapping definition, the depth of the view is not restricted.

The role of views is more important for certain information systems related to national political, economic, technical, military and security interests. Once the basic tables of these systems have been physically designed, a first-level view is immediately established on the base table, which has the same number and structure as the number and structure of the base table. It also stipulates that all programmers are only allowed to operate on the view. Only the database administrator, with a number of people together to master the"Safety Key"To operate directly on the base table.

intermediate tables, reports, and temporary tables
The intermediate table is the table that holds the statistics, which is designed for the data warehouse, the output report, or the query results, and sometimes it has no primary key and foreign key(except Data Warehouse). Temporary tables are designed by programmers to store temporary records that are used by individuals. The base table and the intermediate table areDBAmaintenance, temporary tables are automatically maintained by programmers themselves using programs.

integrity constraints are represented in three ways
Domain integrity: using theCheckto implement constraints, in the database design tool, when you define the range of values for a field, there is aCheckbutton that defines the value of the field through the city.

referential integrity: withPK,FKand table-level triggers to implement.

user-defined integrity: It is a business rule that is implemented with stored procedures and triggers.

the way to prevent database design patching is"Three less principles"

　　(1)the smaller the number of tables in a database, the better. Only the number of tables is small to indicate the systemE-Rfigure Few but good, remove the redundant entities, formed a high degree of abstraction of the objective world, the system of data integration, to prevent the patching style of design;

　　(2)the fewer fields in a table combine primary keys, the better. Because of the role of the primary key, one is to build the primary key index, the second is to do as a sub-table foreign key, so the combination of the number of primary key fields is less, not only saves the running time, but also saves the index storage space;

　　(3)the smaller the number of fields in a table, the better. Only the number of fields is few, it can be explained that there is no duplication of data in the system, and there is little data redundancy, it is more important to urge readers to learn"column is changed to row", which prevents the fields in the child table from being pulled into the main table, leaving many spare fields in the main table. So-called"column is changed to row"is to pull out part of the main table and create a separate child table. This method is very simple, some people are not accustomed to, do not adopt, do not execute.

The practical principle of database design is to find the right balance between data redundancy and processing speed. "Three Little"is a whole concept, a comprehensive view, cannot isolate a certain principle. The principle is relative, not absolute. "more than three"the principle must be wrong. Imagine: If the same functionality is covered by the system, 100 entities(a total of 1000 properties) of theE-Rfigure, certainly more than 200 entities(a total of 2000 properties)of theE-Rpicture, much better.

advocated"Three Little"The principle is to call the reader to learn to use database design technology for system data integration. The steps of data integration are to integrate the file system into the application database, integrate the application database into a subject database, and integrate the subject database into a global consolidated database. The higher the degree of integration, the more data sharing, the less information island phenomenon, the overall enterprise information SystemE-rThe number of entities in the graph, the number of primary keys, and the number of attributes will be less.

advocated"Three Little"The purpose of the principle is to prevent readers from using patching technology, and constantly change the database to make additions and deletions, so that the enterprise database becomes a random design database table"Garbage Heap", or a database table"Clump", resulting in a database of basic tables, code tables, intermediate tables, temporary tables of chaos, countless, leading enterprises and institutions of information systems can not be maintained and paralyzed.

　　"more than three"principles that anyone can do, the principle is"Patching Method"Design Database of the crooked Science said. "Three Little"principle is the principle of few but good, it requires a high degree of database design skills and art, not anyone can do, because the principle is to eliminate the use of"Patching Method"The theoretical basis of database design.

ways to improve the efficiency of database operation

under the condition of the given system hardware and system software, the way to improve the operation efficiency of the database system is:

　　(1)reduce paradigm and increase redundancy in database physical design, use fewer triggers, multi-use stored procedures.

　　(2)when the calculations are very complex and the number of records is very large(For example, 10 million articles), the complex calculation must first be outside the database, in file system mode withC + +after the processing of the language calculation is completed, the final storage is appended to the table. This is the experience of telecom billing system design.

　　(3)If you find too many records for a table, such as more than 10 million, you want to split the table horizontally. The horizontal split is done with the table primary keyPKa value of a line, dividing the table's records horizontally into two tables. If you find that there are too many fields for a table, such as more than 80, split the table vertically and break the original table into two tables.

　　(4)to the database management systemDBMSsystem optimization, which optimizes various system parameters, such as the number of buffers.

　　(5)when using data-orientedSQLwhen programming language, try to take optimization algorithm.

In a word, in order to improve the efficiency of database operation, we must optimize the database system level, the database design level and the program implementation level, and work hard at the same time on three levels.

The above 14 skills, is a lot of people in a large number of database analysis and design practice, gradually summed up. The use of these experiences, readers can not help hard sets, rote memorization, and to digest understanding, pragmatic, flexible grasp. and gradually achieve: in the application of development, in the development of the application.

From the network

Database Design (RPM)