MySQL Database Design Notes and summary (1) Requirements analysis

Steps for Database design

① Demand Analysis

② Logic Design

Logical modeling of a database using ER diagrams

③ Physical Design

④ Maintenance Optimization

A. New requirements for the building of a table

B. Index optimization

C. Large table splitting

"Demand Analysis"

① Understanding the data to be stored in the System (object/entity)

A. Relationship between entities and entities (1 pairs of multiple, many-to-many)

B. What are the attributes that the entity contains

C. Which properties or combinations of attributes can uniquely identify an entity

② Understanding the storage characteristics of data

③ Understanding the life cycle of data

Cases

A small e-commerce website, the core modules include: users, products, orders, shopping carts, suppliers .

① User Module

include attributes: username, password, phone, email, id, address, name, nickname

Optional Unique identity attribute : User name, ID, phone

Storage Features: with the system on-line time increases gradually, need to store permanently

② Commodity Module

include attributes: product code, product name, product description, product category, supplier name, weight, expiry date, price

Optional Unique identity attribute:(product name, vendor name combination), (product code)

Storage Features: for offline products can be archived storage

③ Order Module

include attributes: order number, user name, user phone, shipping address, item number, product name, quantity, price, order status, payment status, order type

Optional Unique identity attribute:(order number), (user name, product, combined attribute of the order date)

Storage Features: Permanent storage (sub-table, library storage)

④ Shopping Cart The user saves the item that the user has selected when shopping

include attributes: user name, product number, product name, commodity price, product description, Commodity category, number of items

attributes can be uniquely identified:(user name, item number, join time), (Shopping cart number)

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

⑤ supplier information for the products sold by the vendor

include attributes: vendor number, vendor name, contact, phone, business license number, address, legal entity

Optional Unique identification:(supplier number), (Business license number)

Storage Features: permanent storage

"Relationship between modules"

User ← 1 to many → orders

↑↑

1-to -many- to-many

↓↓

Shopping cart ← Many-to-many → products ← Many-to-many → suppliers

MySQL Database Design Notes and summary (1) Requirements analysis