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