Three paradigms of database (relational type) design

In order to establish a small data redundancy , there is no data exception (update, delete, insert) of the database. You must follow certain rules when designing your 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.

When designing a database, follow the following three paradigms to meet business needs.

first normal form (1NF): All fields in a database table are single attributes and cannot be divided.

Explanation: The first paradigm requires that the tables in the database are two-dimensional tables. In general, a relational database can only design a two-dimensional table.

For example, the following is a table that violates the first paradigm. Because its "User information" field can be re-split.

User Information table

User ID	Account	Password	User Information
1	Zhangsan	******	Name	Cell phone
			Tom	13588888888

The table after the User information field has been split.

                                                  User Information table

User ID	Account	Password	Name	Cell phone
1	Zhangsan	******	Tom	13588888888

second Normal form (2NF): A non-critical field in a database table does not have a partial function dependency on any of the candidate key fields.

Explanation: A partial function dependency is a case where a key field that has a combined key field determines a non-critical field. As long as you ensure that each field in the table is related to the key field, it conforms to the second normal.

For example, the following is a table that violates the second paradigm.

Community Join Information Form

Students	Societies	Join time	About the Society	Gender
Tom	Animation Club	2015.9.30	The gathering place of the anime fans	Man
Newcomer	Animation Club	2015.9.29	The gathering place of the anime fans	Woman

The relationship between students and the community is that a student can join a number of clubs, a community can also have multiple students, that is, many-to-many relationships.

The student field is combined with the Community field to determine the Join Time field. So this is a table of key fields ("Student", "community").

However, "student" determines "gender", "community" determines "community profile", so there is a key field of the combined key field determines the situation of non-critical fields.

Problems with tables that violate the second paradigm:

1. Insert exception

If there is no one to join the animation club, then it does mean that the animation club profile is not there.

2. Delete exception

If the animation club students are deleted, is not meant that the animation club profile is not there.

3. Update exception

If you want to update the animation club profile, is not to update multiple records.

4. Data redundancy

Obviously, the animation club's profile has a number of brief information.

The following is a table that is split by the second normal form.

Student table

Students	Gender
Tom	Man
Newcomer	Woman

Society table

Societies	About the Society
Animation Club	The gathering place of the anime fans

                                                 Student and Community relations table

Students	Societies	Join time
Tom	Animation Club	2015.9.30
Newcomer	Animation Club	2015.9.29

third Normal form (3NF): There is no non-critical field in the database table for the transfer function dependency of any candidate key field.

Explanation: A transitive function dependency refers to a candidate critical field that indirectly determines a non-critical field.

For example, the following is a table that violates the third paradigm.

Product Information Sheet

Product Name	Classification	Category description
Wolf fur coat	Coat	A dress that can fit X.

"Commodity name" determines "classification", however "classification" determines "classification description". Therefore, there is a non-critical field "categorical description" for the Candidate key field "commodity name" transfer function dependency.

namely: "Product Name"---> "classification"---> "category description"

"Classification description" is indirectly related to "trade name".

Product Information Sheet

Product Name	Classification	Category description
Wolf fur coat	Coat	A dress that can fit X.

The following is a table broken down by the third paradigm.

Classification Information table

Classification	Category description
Coat	A dress that can fit X.

Product Information Sheet

Product Name	Classification
Wolf fur coat	Coat

Three paradigms of database (relational type) design