Getting Started with database design

The database is the foundation of the network application, the good table structure design, plays the vital role to the entire application.

Steps for Database design:
1. Requirements Analysis: What the data is, what properties, data and attributes are characteristic
2. Logical design: Logical modeling of a database using ER diagrams
3. Physical design: Select a database system and transform the logical design
4. Maintenance optimization: Append, spin-off, etc.

Example Demo (e-commerce site)

First, demand analysis:

User module: Used to log in and save user information, etc.
Attributes (user name, password, phone, mailbox, ID, address, name ...) ) Unique identification (user name, ID, phone) storage characteristics (permanent)

Commodity module: Used to record the product information sold in the website
attributes (encoding, name, description, classification, vendor, Price ... ) Unique identification (encoding, name + Vendor) storage characteristics (not available in the sale of goods can be archived)

Order module: Information for ordering products by users
Attribute (order number, recipient, phone, address, product name and quantity, order status, payment status) Unique identification (order number) storage characteristics (sub-table sub-Library)

Shopping Cart module: Used to save the product that the user selected when shopping
Attribute (number, user name, product number name quantity price, join time) Unique identification (shopping cart number) storage characteristics (no permanent, can be scheduled to archive and clean)

Supplier Module: The supplier information used to store the items sold
Attribute (number, name, contact, business license number) Unique identification (number, business license number) storage characteristics (permanent)

Second, logical design:

ER diagram: Rectangle (Entity), Diamond (contact set), ellipse (attribute "underline as primary Key"), segment (connection)
A contact set is primarily used to convert a many-to-many relationship into a one-to-many (that is, to create a relational table)

Database design Paradigm:

First paradigm: Each column property is an indivisible atomic data item (that is, each attribute cannot be divided).
Case: The address is divided into provinces, cities, counties, detailed (Street house), four inseparable parts.

Second paradigm: Requires that the attributes of an entity depend entirely on the primary key (that is, a unique primary key is established for each entity).
Case: Student Information Sheet, study number + course → results, results can not be completely dependent on the primary key number.

Third paradigm: On a 2NF basis, any non-primary attribute is not dependent on other non-principal attributes (eliminating transfer dependencies on a 2NF basis, reducing data redundancy)
Case: In the Employee Information table, after you add the department number, you can no longer add the attribute of the dependent department number such as introduction to the department name.

BC Paradigm: On a 3NF basis, any non-primary attribute cannot be dependent on the primary key subset (eliminating dependency on a subset of the main code on a 3NF basis)

Third, physical design

1, select the appropriate database system

2. Define naming conventions for databases, tables, and fields

3. Set the field type according to the database system (priority number type, second date and binary, last string)

4, anti-paradigm design (convenient query, improve efficiency)

Column type	Storage space
TINYINT	1 bytes
SMALLINT	2 bytes
Mediumint	3 bytes
Int	4 bytes
Bingint	8 bytes
DATE	3 bytes
Daretime	8 bytes
TIMESTAMP	4 bytes
CHAR (M)	M bytes, 1<=m<=255
VARCHAR (M)	l+1 bytes, l<m&&1<=m<=255

Iv. Maintenance and optimization

1. Maintain Data dictionary

2. Maintenance Index

3, maintenance of the table structure

4. Split of table (vertical, horizontal)

Vertical split principle: Splits common fields and infrequently used fields by ID primary key into two or more tables, reducing the width of a table

Horizontal split principle: Split the history or outdated data horizontally into multiple tables, reducing the length of the table

Getting Started with database design