Database Design-practice, database-Practice

Database Design-practice, database-Practice
Objectives and objectives

Objective:

• Master routine database tabulation operations
• Understand the table design specifications

What is table structure design:

• Table structure design must be completed before formal development
• Abstract complex business models based on product requirements

Project Analysis Practical course model:

• Websites similar to e-commerce websites

User: core business:

• Transaction-related
  • Order details, order
• Product-related
  • Product category, product

Non-core business:

• Logistics
• Supplier information

  Business Model to table

 

User: tb_account (User table) product class: tb_goods_category (item category table) tb_goods (item Details table) Transaction class: tb_order_item (transaction order Details table) tb_order (transaction table) what should I pay attention to when designing tables?

• Understanding the dependencies between tables
• Understand the features of each table (constraints between fields, indexes, field types, and field lengths)

Collection table attribute User table:

 

Auto-increment attributes: auto_increment images are not directly stored in the database, and an image url is stored in the database, there is a dedicated image storage system to store image varchar VS text. When storing extremely long fields, it generally involves two aspects: 1. performance 2. the maximum storage length of varchar is 65535. If utf8 is used and 3 bytes are used, the maximum varchar size is 65535/3 = 2184565535 and the 16th power Netease data type of varchar is 2-naming convention

• All table names and Field Names Use lowercase letters
• Different services, table names are differentiated by different prefixes
• Generating Environment table names field names must be meaningful
• Use the full name of a single field as much as possible; Separate multiple field names with underscores

Field design specifications:

• Storage space (select the minimum data type that meets application requirements as much as possible)
• Prospective (use integer type instead of balanced type as much as possible)
• Note: Each field must use the COMMENT statement to give the role of the field.
• Frequently accessed large fields (such as blob and text) need to be put into a single table separately, to avoid reducing SQL efficiency, pictures, movies, and other large file data cannot be stored in the database
• We recommend that you use the utf8mb4 character set for new businesses (applicable to the imoge emoticons library of Apple mobile phones)

Note:

• Understand what permissions a user needs
  • Normal users only have read and write permissions
  • The system administrator has super permissions.
• Permission granularity should be as detailed as possible
  • Do not set the with grant option attribute for normal users.
  • Permission granularity: system level> database level> surface level> field level
• Simple Password prohibited
  • Random online password requirements

Table Structure

---- Table structure for table `tb_account`-- DROP TABLE IF EXISTS `tb_account`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb_account` (  `account_id` int(11) NOT NULL AUTO_INCREMENT,  `nick_name` varchar(20) DEFAULT NULL,  `true_name` varchar(20) DEFAULT NULL,  `sex` char(1) DEFAULT NULL,  `mail_address` varchar(50) DEFAULT NULL,  `phone1` varchar(20) NOT NULL,  `phone2` varchar(20) DEFAULT NULL,  `password` varchar(30) NOT NULL,  `create_time` datetime DEFAULT NULL,  `account_state` tinyint(4) DEFAULT NULL,  `last_login_time` datetime DEFAULT NULL,  `last_login_ip` varchar(20) DEFAULT NULL,  PRIMARY KEY (`account_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */; ---- Table structure for table `tb_goods`-- DROP TABLE IF EXISTS `tb_goods`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb_goods` (  `goods_id` bigint(20) NOT NULL AUTO_INCREMENT,  `goods_name` varchar(100) NOT NULL,  `pic_url` varchar(500) NOT NULL,  `store_quantity` int(11) NOT NULL,  `goods_note` varchar(800) DEFAULT NULL,  `producer` varchar(500) DEFAULT NULL,  `category_id` int(11) NOT NULL,  PRIMARY KEY (`goods_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */; ---- Table structure for table `tb_goods_category`-- DROP TABLE IF EXISTS `tb_goods_category`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb_goods_category` (  `category_id` int(11) NOT NULL AUTO_INCREMENT,  `category_level` smallint(6) NOT NULL,  `category_name` varchar(500) DEFAULT NULL,  `upper_category_id` int(11) NOT NULL,  PRIMARY KEY (`category_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */; ---- Table structure for table `tb_order`-- DROP TABLE IF EXISTS `tb_order`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb_order` (  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,  `account_id` int(11) NOT NULL,  `create_time` datetime DEFAULT NULL,  `order_amount` decimal(12,2) DEFAULT NULL,  `order_state` tinyint(4) DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  `order_ip` varchar(20) DEFAULT NULL,  `pay_method` varchar(20) DEFAULT NULL,  `user_notes` varchar(500) DEFAULT NULL,  PRIMARY KEY (`order_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */; ---- Table structure for table `tb_order_item`-- DROP TABLE IF EXISTS `tb_order_item`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `tb_order_item` (  `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,  `order_id` bigint(20) NOT NULL,  `goods_id` bigint(20) NOT NULL,  `goods_quantity` int(11) NOT NULL,  `goods_amount` decimal(12,2) DEFAULT NULL,  PRIMARY KEY (`order_item_id`),  UNIQUE KEY `uk_order_goods` (`order_id`,`goods_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;