Database design Principles

Yesterday someone asked me database design principles, more than a year did not do service end this piece also basically forget clean, remember what two level, three level what (do not misunderstand this is not the result of the yellow film to see much), the university also learned the database, the university also from the requirements to the database design to realize independently completed a project, I'm still sure about this database, like what stored procedure triggers transaction locks, and so on, the knowledge of these theories is forgotten almost, may be hidden in the depths of the brain. Today, the online search, the original level three is the paradigm (genius I can still remember from the mind). Here's a look at the 3-level paradigm.

1. First paradigm (ensuring that each column remains atomic)

The first paradigm is the most basic paradigm. If all the field values in a database table are non-exploded atomic values, the database table satisfies the first paradigm.

such as the user's address, the system can be designed to use only one field for example: Shenzhen South Mt. District Chinese communication a block XXX number. But the system will often visit the address of the city, for example, in the Express form, fill out the express order will be issued when the city so convenient courier flow, so express find these are also very convenient. That would be necessary to find the address of the following: Province, City, district, detailed address. We also fill in the shipping address on some shopping websites.

2. Second paradigm (ensure that each column in the table is related to the primary key)

The second paradigm is based on the first paradigm in a more advanced layer. The second paradigm needs to ensure that each column in a database table is related to the primary key, not just one part of the primary key (primarily for the Federated primary key). In other words, in a database table, only one data can be saved in a table, and multiple data cannot be saved in the same database table.

This is also an example of shopping. For example, the user orders will generate an order. The order form may include: Order ID, Product ID, user ID, quantity, receipt address ID, Unit price, courier number, etc. An order will have multiple items, is a one-to-many relationship, the order ID and the product ID belong to the Federated primary key, but the delivery address, the courier number and order ID is a one-on relationship. Therefore, the above should be divided into two tables: an order to ship the table, an order schedule. The shipping list mainly includes the order ID, the receipt address ID, the courier number and so on, and the detailed list includes the order ID, Product ID, user ID Quantity, unit price and other information.

3. Third paradigm (ensure that each column is directly related to the primary key column, not indirectly)

Frequently encountered field type types, such as commodity types: daily necessities, household appliances and other categories. Add the product type directly to the attribute in the commodity table: Product ID, product name, commodity type ID, commodity type name, etc. This kind of design I also have seen may think is for the sake of convenience, in fact such a point is not convenient, will appear redundant. The ideal design is to separate the product type into a table: commodity type ID, commodity type name.

4. Simple understanding of Level three paradigm

The first paradigm: 1NF is an atomic constraint on attributes, requiring attributes to be atomic and non-decomposed.
The second paradigm: 2NF is a unique constraint on records, requiring records to have a unique identity, that is, the uniqueness of the entity;
The third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from another field, it requires no redundancy in the field. 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

5. Considerations for Database Design

1. Note the relationship between the original document and the entity and the relationship between the entity and the entity

The relationship between the original document and the entity: for example, an order, there may be an order delivery information table, an order BOM. A monomer is made up of two entities.

The relationship between entity and entity: the same as the relationship between the original data and the entity, there is a one-to-many, single-to-many-to-many relationship, a one-to-many joint primary key, many-to-many words are divided into a pair of more than two tables to achieve.

2. Primary key

Primary key sub-business primary key and logical primary key. Sometimes setting the primary key correctly may reduce database redundancy. Or use the above order example. When we generate an order, we can use the Product ID, the user ID setting plus the time to calculate the federated primary key. However, each order on the table can not be added to the above several fields to obtain it, it will be more troublesome. The order ID is a logical primary key (self-increment type) can also be regarded as business primary key (such as ID is a certain rules splicing with a certain business rules), it is more convenient to find an order.

3. Constraints

Constraints are primarily intended to prevent data errors.

The integrity of the domain: Age is to be in a certain range and so on.

Referential integrity: The use of PK, FK, table-level triggers to achieve. The data must have a reference, within the reference is the correct data.

User-defined integrity: It is a business rule that is implemented with stored procedures and triggers. For example, when inserting data, data is sometimes subject to certain rules.

5. Summary

At the end of the fact that these are theoretical knowledge, the most important thing is to realize their own understanding of the summary. I also belong to the blind force, nor how comprehensive, welcome everyone to spit Groove exchange.

Database design principles