Database design that's the thing.

Purpose: Effective storage, efficient access.

Excellent design features

1. Reduce data redundancy, 2. Avoid data anomalies, 3. Save storage space, 4. Efficient access to data

Database design Steps

1. Requirements analysis, 2. Logical Design ER modeling, 3. Physical Design (Mysql, Oracle, SQL Server), 4. Maintenance optimization (new requirements build table, index optimization, large table split).

Demand analysis

Figuring out the relationship between entities and entities?

What attributes does the entity contain?

What is the unique identity of the entity?

For the entity of the log class, the sub-database can be designed and archived regularly.

E-commerce example, user module, commodity module, order module, shopping cart module, supplier module.

User module, including attributes: User name, password, phone, mailbox, ID, address, name, nickname ...

Optional unique identification, user name, ID, phone, mailbox.

Storage features, gradually increase with the system on-line, need permanent storage.

Commodity module, including attributes: Product code, product name, product description, commodity classification, supplier name, price

Optional unique identification (product code), (product name, supplier name)

Storage Features: For offline products can be archived storage (do not delete).

Order module, including attributes: Order number, user name, phone, shipping address, product number, product name, quantity, price, order status, payment status, order type ...

A unique identification, order number is available.

Storage Features: Permanent storage (sub-Library sub-table)

Shopping cart module, including attributes: User name, product number, product name, commodity price, commodity classification, time of entry, number of items ...

Optional Unique identification: (user name, item number, join time), (cart number)

Storage Features: No permanent storage (set archive, cleanup rules)

Vendor module, including attributes: Vendor number, vendor name, contact, phone, business license number, address, legal entity ...

Optional Unique identification: Supplier number, business license number

Storage Features: Persistent storage.

Logic Design

Rectangles represent entity sets

Diamond represents a contact set

Ellipses represent entity properties

Segment connection properties with entities, entities, and contact sets

Follow the 123 paradigm is basically enough, follow it can effectively avoid database operation anomaly and data redundancy.

The first normal form (1NF), the field is not split. each field in the table is the smallest data unit

The following design does not conform to the first paradigm.

The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. On the basis of 1NF, all non-code attributes in the table must be "completely dependent" on the candidate code and cannot be partially dependent

There is a partial dependency on the main property.

Commodity price and weight, validity, classification, depend on the commodity.

Vendor phone, dependent on the vendor name.

Split into 3 tables. Commodity table, Supplier table, Supplier and Commodity Association table.

or split into 2 tables. Commodity table, Supplier table. Commodity table associated Supplier table.

The third paradigm (3NF) is that the same information cannot be stored repeatedly. On a 2NF basis, there is no interdependence between non-primary attributes (elimination of transitive dependencies)

Classification description and classification exist transitive dependencies.

Split into commodity tables, classification tables. The classification is associated with the category ID.

There is no function dependency between BC paradigm (BCNF) Composite keywords

Split into vendor, vendor contact

Product ID, Vendor Contact, number of items

Physical design

Choose the right database management system, MYSQL, Oracle, SQL Server.

Establish databases, tables, and naming conventions.

Depending on the DBMS selected, select the appropriate field type.

Inverse paradigm design, redundancy, space-changing time.

The principles of the principle of reading, the principle of ideographic, the principle of long name. The following table naming is not advisable.

For dates, precedence, int,datetime,char, varchar.

avoid using FOREIGN KEY constraints , which affect high concurrency. (Reduce import efficiency and increase maintenance costs).

Avoid using triggers (data anomalies may occur and business logic becomes complex).

inverse Paradigm , space-changing time, suitable for high concurrent projects.

Reduce the number of table associations

Increased read efficiency

Anti-normalization to moderate amount of

Maintenance optimization

Maintain data dictionaries (especially status fields) and add notes

Maintain indexes (increase index, optimize index)

Table Structure Optimization

Horizontal split, vertical split table

Often checked in a table. Not commonly used, large pieces of the place into another table. (split vertically) to solve the problem of large table widths.

Horizontal splits are achieved by hashing. Solve the problem of large data volume.

The specific design, but also according to the business analysis. Try to design the right table. Good ground floor, to build a solid building. At the end of the maintenance period, the design steps and principles should be observed as much as possible. Let the system improve methodically.

Database design that's the thing.