Basic design of database table based on third normal form

This paper first discusses the basic design of database table based on the third normal form, emphatically discusses the strategy and scheme of establishing primary key and index, and then summarizes the optimization scheme of database management system from the view of the expansion design of database table and the placement of library Table object.

1. Introduction

The goal of database optimization is to avoid disk I/O bottlenecks, reduce CPU utilization, and reduce resource competition. In order to facilitate readers to read and understand, the author referred to Sybase, Informix and Oracle and other large database system reference materials, based on years of engineering experience, from the basic table design, extended design and database table object placement and so on, discussed emphatically how to avoid disk i/ o Bottlenecks and reduce resource competition, I believe readers will be at a glance.

2. Basic table design based on the third paradigm

In the table-driven information management system (MIS), the basic table Design code is the third normal form (3NF). The basic feature of the third paradigm is that non-primary key attributes depend only on primary key properties. The database table design based on the third normal form has many advantages: one is to eliminate redundant data, save disk storage space, the second is a good data integrity restrictions, that is, based on the main foreign key referential integrity restrictions and based on the primary key entity integrity restrictions, which makes the data easy to maintain, but also easy to transplant and update; third, the data is reversible good, Do not omit the connection (join) query or merge table, also does not duplicate; Because redundant data (redundant columns) is eliminated, and more rows of data are stored in each data page in a query (Select), this effectively reduces logical I/O, with more pages per cash, and less physical I/O Five is for most transactions (Transaction), the performance is good, six is the physical design (physical designs) The maneuverability is big, can satisfy the increasing user demand.

In the basic table design, the table's primary key, the foreign key, the index design occupies the very important position, but the system design personnel often only pays attention to satisfies the user request, but did not from the system optimization height to recognize and to attach importance to them. In fact, they are closely related to the operational performance of the system. These basic concepts and their significance are now discussed from the perspective of System Database optimization:

(1) Primary key (Primary key): When a primary key is used in a complex SQL statement, it is frequently used in data access. A table has only one primary key. Primary keys should have fixed values (cannot be null or default, have relative stability), do not contain code information, easy access. It makes sense to use common (well known) columns as primary keys. Short primary keys are best (less than 25bytes), the length of the primary key affects the size of the index, and the size of the index affects the size of the index page, which affects disk I/O. Primary key is divided into natural primary key and human primary key. Natural primary key consists of the attributes of the entity, natural primary key can be complex, in the formation of composite primary key, the primary key column can not be too much, compound primary key makes join* complicate, also increased the size of the foreign key table. The human primary key is artificially formed when there is no suitable natural attribute key, or if the natural attribute is complex or highly sensitive. The human primary key is generally an integer value (satisfies the minimum requirement), has no practical significance, and also slightly increases the size of the table, but reduces the size of the table that takes it as a foreign key.

(2) foreign key (Foreign key): The role of foreign key is to establish the relationship between the tables in the relational database (referential integrity), the primary key can only be migrated from the independent entity to the independent entity, as a property of the latter, known as the foreign key.

(3) Indexes (Index): Using index to optimize system performance is obvious, and indexing all columns and all columns used to sort the WHERE clause in a query avoids the entire table scan or access, and directly accesses a particular data column without altering the physical structure of the table. This reduces data access time, and uses indexes to optimize or eliminate time-consuming classifications *; spreading data across different pages distracts the inserted data; The primary key automatically establishes a unique index, so the unique index ensures the uniqueness of the data (i.e. entity integrity); The smaller the index code, the more direct the location. ; The new index is the best, so it is necessary to update the index regularly. Indexes also have a price: there is a space overhead, and it takes time to build it, as well as maintenance costs when making inserts, deletes, and update*. There are two kinds of indexes: the clustered index and the non-clustered index. A table can have only one clustered index and multiple nonclustered indexes. Using a clustered index to query data is faster than using a nonclustered index. Before building an index, you should use the database system function to estimate the size of the index.