Advanced design of the database

I. Standardization

In the design of database, there is a tradeoff between minimizing data redundancy and database efficiency.

1. First Paradigm:

• Define the required data items and place the related data items in a table.
• Make sure there are no duplicate data groups. (You can delete duplicate data by dividing the data into multiple tables)
• Make sure there is a primary key.

Primary key: Unique identifier of the record. You can add a new column, or use one or more of the existing columns, as long as the columns can be combined into a unique primary key.

2. Second paradigm: Requires no local correlation for any of the columns in the primary key.

3. Third paradigm (optional, environment dependent): conforms to the second paradigm, and all non-primary key fields are dependent on the primary key.

Transitive correlation. Eliminate transitive dependencies--place data items with transitive dependencies in a single table.

The benefits are reduced data duplication, data integrity, and the risk of updating only certain data when duplicate data changes are avoided.

The downside: increased complexity and reduced efficiency.

Two. Use constraints to ensure the validity of data

Advanced design of the database