Getting started with database design and getting started with databases

Getting started with database design and getting started with databases

Database is the foundation of network applications. A good table structure design plays a vital role in the entire application.

Database design steps:
1. Requirement Analysis: What is data, what attributes are there, and the characteristics of data and attributes
2. Logical design: Use the erdiagram to perform logical modeling for the database
3. Physical Design: select a database system and convert the logical design.
4. maintenance optimization: append and split

 

Demo (e-commerce website)

I. Requirement Analysis:

User Module: Used to log on to and save user information.
Attributes (username, password, mobile phone number, email address, ID card, address, and name ...) Unique Identity (user name, ID card, mobile phone) storage features (permanent)

Product module: used to record the product information sold on the website
Attributes (encoding, name, description, classification, supplier, price ...) Unique Identifier (encoding, name + supplier) storage features (non-sold goods can be archived)

Order module: Used for the user to order product information
Attributes (Order Number, recipient, phone number, address, product name and quantity, order status, payment status) Unique Identifier (Order Number) storage features (Table sharding)

Shopping Cart module: used to save the items selected during user shopping
Attribute (number, user name, product number name quantity price, join time) Unique Identifier (shopping cart number) storage features (no permanent, regular archiving and cleaning)

Supplier module: used to store the supplier information of the sold goods
Attribute (ID, name, contact, business license number) Unique Identifier (ID, business license number) storage features (permanent)

 

Ii. Logic Design:

ERTU: rectangle (entity), Diamond (contact set), elliptic (attribute [primary key with underline]), line segment (connection)
The contact set is mainly used to convert many-to-many relationships to one-to-many (that is, to create a relational table)

Database design paradigm:

Paradigm 1: Each attribute column is an inseparable atomic data item (that is, each attribute cannot be further divided ).
Case: The address is divided into provinces, cities, districts and counties, and details (street signs.

Second paradigm: requires that the attributes of an object fully depend on the primary key (that is, each object is distinguished by a unique primary key ).
Case: In the student information table, student ID + course → score cannot depend entirely on the primary key student ID.

Third paradigm: Based on 2NF, any non-primary attribute does not depend on other non-primary attributes (based on 2NF, transmission dependencies are eliminated to reduce data redundancy)
Case: In the employee information table, after adding a department number, you cannot add the Department name and other attributes of the dependent Department number.

BC paradigm: Based on 3NF, any non-primary attribute cannot depend on the primary key subset (dependency on the primary key subset is eliminated Based on 3NF)

 

Iii. Physical Design

1. select an appropriate database system

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

3. Set the field type based on the database system (Priority numeric type, followed by date and binary, and last string)

4. Anti-paradigm design (convenient query and improved efficiency)

Column Type	Storage space
TINYINT	1 byte
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 byte, L <M & 1 <= M <= 255

 

 

 

 

 

 

 

 

 

 

 

 

 

Iv. Maintenance Optimization

1. Maintain the data dictionary

2. Maintain Indexes

3. Maintained table structure

4. Table splitting (vertical and horizontal)

Vertical Split principle: splits common fields and infrequently used fields into two or more tables based on the id primary key to reduce the table width.

Horizontal Split principle: splits historical or expired data horizontally into multiple tables to reduce the table length.