Knowledge of design paradigm of database

Source: Internet
Author: User

In order to build a database with small redundancy and reasonable structure, we must follow certain rules when designing database. In a relational database, this rule is called a paradigm. A paradigm is a summary of a design requirement. In order to design a relational database with reasonable structure, a certain paradigm must be met.

The most common design paradigms in real-world development are three:

1 . First Paradigm

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.

The rational follow-up of the first paradigm needs to be determined according to the actual needs of the system. For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a database table field. However, if the system often accesses the "city" part of the "address" attribute, then it is not to be the "address" attribute to be re-split into provinces, cities, detailed address and other parts of storage, so that in the address of a part of the operation will be very convenient. This design satisfies the first paradigm of the database, as shown in the following table.

User Information table

number

name

sex

age

contact phone

province

City

detail address

1

Zhang Hongxin

Man

26

0378-23459876

Henan

Kaifeng

No. 23rd Xinhualu, Chaoyang District

2

Li Shiping

Woman

32

0751-65432584

Guangzhou

Guangdong

No. 148th, Tianming Road, Baiyun District

3

H

Man

21st

0371-87659852

Henan

Zhengzhou

No. 198th Erqi District University Road

4

Guo Xiaoming

Woman

27

0371-62556789

Henan

Zhengzhou

No. No. 218, Xinzheng bei Jie, Xue Dian

The user information shown in the table above follows the requirements of the first paradigm, which makes it very convenient to classify users using cities, and also improves the performance of the database.

2 . Second Paradigm

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.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key for the database table, as shown in the following table.

order Information Form

Order number

Product number

Product Name

Number

Unit

Price

Customer

Affiliated units

Contact information

001

1

Excavator

1

Console

1200000¥

Tom

On Hai Shi

020-1234567

001

2

Impact Drills

8

Put

230¥

Tom

On Hai Shi

020-1234567

002

3

Forklift

2

Car

980000¥

John doe

Beijing Company

010-1234567

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, the Order Item table is also separated into another table, it is perfect. as shown below.

order Information Form

Order number

Customer

Affiliated units

Contact information

001

Tom

On Hai Shi

020-1234567

002

John doe

Beijing Company

010-1234567

Order Item table

Order number

Product number

Number

001

1

1

001

2

8

002

3

2

Product Information Sheet

strong> units

Impact Drill

valign= width= "Top" and "$" > TD valign= "Top" width= "ten" >

Product Code

Product name

product price

1

Excavator

Table

/td>

1200000 ¥

2

230 ¥

Forklift

Cars

980000 ¥

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

3 . Third Paradigm

The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, not indirectly.

For example, when designing an order data table, the customer number can be used as a foreign key and order table to establish the corresponding relationship. Instead of adding fields to the order form about other customer information (such as name, company, etc.). The design shown in the following two tables is a database table that satisfies the third paradigm.

Order Information Form

Order number

Order Items

Head

Salesman

Order Quantity

Customer number

001

Excavator

Liu ming

Partner

1 units

1

002

Impact Drills

Li Gang

Hownie

8 x

2

003

Forklift

Guo Niu

Amelie

2 Cars

1

Customer Information Form

customer number

customer Name

owned company

Contact Information

1

Lee Cong

51 construction

13253661015

2

Liu Xinmin

self-employed

13285746958

In this way, when the order information is queried, the customer number can be used to refer to the records in the Customer information table, and the data redundancy is reduced without having to enter the contents of the customer information multiple times in the Order Information table.

Knowledge of design paradigm of database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.