MyBatis Study Notes (9)-Order commodity Data Model analysis

MyBatis Study Notes (9)-Order commodity Data Model analysis

• MyBatis Learning Note 9-Order commodity data Model analysis
  • Data Model Analysis Ideas
  • Data Model Analysis
  • Order commodity Data Model build table SQL

This article analyzes the data models used in the next few blogs and attaches the SQL file and the test data file.

Data Model Analysis Ideas

• The data content recorded for each table

The sub-module is familiar with the contents of each table record, which is equivalent to the process of learning the system requirements (functions).

• Important field settings for each table

Non-null field, foreign key field

• Relationship between a database-level table and a table

FOREIGN key relationships

• A business relationship between a table and a table

When you analyze the business relationship between tables and tables, you must build on a business sense basis to analyze them.

Data Model Analysis

• User table users: records the user information of the purchased item
• Order Form orders: Orders created by the user are recorded (orders for goods purchased)
• Order Schedule OrderDetail: The details of the order are recorded and information about the purchase of the goods
• Product List items: Record the product information

A business relationship between a table and a table:

The analysis of the business relationship between tables and tables needs to be built on a business sense basis. Analyze the business relationship between tables that have a relationship between data levels first:

• Usre and Orders:

User-->orders: A user can create multiple orders, one-to-many
Orders->user: An order is created by only one user

• Orders and OrderDetail:

Orders->orderdetail: An order can include multiple order details, because an order can buy multiple items, each item's purchase information is recorded in the OrderDetail, a one-to-many relationship

Orderdetail–> Orders: An order detail can only be included in one order

• OrderDetail and ITESM:

Orderdetail->itesms: An order detail corresponds to one product information

Items–> OrderDetail: A product can be included in multiple order details, one-to-many

Re-analyze whether there is a business relationship between tables that do not have a relationship at the database level:

• Orders and items:

Relationships can be established between orders and items through the OrderDetail table.

Order commodity Data Model build table SQL

/*sqlyog v10.2 mysql-5.1.72-community:database-mybatis****************************************************** ****************//*!40101 SET NAMES UTF8 * /;/*!40101 SET sql_mode= ' * *;/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 * /;/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * /;/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * /;/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 * /;/*table structure for Table ' items ' * /CREATE TABLE ' Items '(' id ' int( One) not NULLAuto_increment,' name ' varchar( +) not NULLCOMMENT' Product name ',' Price ' float(Ten,1) not NULLCOMMENT' Commodity pricing ',' Detail 'Text COMMENT' Product description ',' pic ' varchar( -)DEFAULT NULLCOMMENT' Product Pictures ',' Createtime 'Datetime not NULLCOMMENT' production date ',PRIMARY KEY(' id ')) Engine=innodb auto_increment=4 DEFAULTCharset=utf8;/*table structure for Table ' orderdetail ' * /CREATE TABLE ' OrderDetail '(' id ' int( One) not NULLAuto_increment,' orders_id ' int( One) not NULLCOMMENT' Order ID ',' items_id ' int( One) not NULLCOMMENT' Product ID ',' Items_num ' int( One)DEFAULT NULLCOMMENT' quantity of goods purchased ',PRIMARY KEY(' id '),KEY ' Fk_orderdetail_1 '(' orders_id '),KEY ' fk_orderdetail_2 '(' items_id '),CONSTRAINT ' Fk_orderdetail_1 ' FOREIGN KEY(' orders_id ')REFERENCES ' orders '(' id ') on DELETE NO ACTION  on UPDATE NO ACTION,CONSTRAINT ' fk_orderdetail_2 ' FOREIGN KEY(' items_id ')REFERENCES ' Items '(' id ') on DELETE NO ACTION  on UPDATE NO ACTION) Engine=innodb auto_increment=5 DEFAULTCharset=utf8;/*table structure for Table ' orders ' * /CREATE TABLE ' orders '(' id ' int( One) not NULLAuto_increment,' user_id ' int( One) not NULLCOMMENT' next single User ID ',' number ' varchar( +) not NULLCOMMENT' Order number ',' Createtime 'Datetime not NULLCOMMENT' Create order time ',' Note ' varchar( -)DEFAULT NULLCOMMENT' remarks ',PRIMARY KEY(' id '),KEY ' Fk_orders_1 '(' user_id '),CONSTRAINT ' fk_orders_id ' FOREIGN KEY(' user_id ')REFERENCES ' user '(' id ') on DELETE NO ACTION  on UPDATE NO ACTION) Engine=innodb auto_increment=6 DEFAULTCharset=utf8;/*table structure for Table ' user ' * /CREATE TABLE ' user '(' id ' int( One) not NULLAuto_increment,' username ' varchar( +) not NULLCOMMENT' User name ',' Birthday ' Date DEFAULT NULLCOMMENT' Birthday ',' Sex ' Char(1)DEFAULT NULLCOMMENT' Sex ',' Address ' varchar( the)DEFAULT NULLCOMMENT' Address ',PRIMARY KEY(' id ')) Engine=innodb auto_increment= - DEFAULTCharset=utf8;/*!40101 SET [email protected]_sql_mode * /;/*!40014 SET [email protected]_foreign_key_checks * /;/*!40014 SET [email protected]_unique_checks * /;/*!40111 SET [email protected]_sql_notes * /;

Test data

/*sqlyog v10.2 mysql-5.1.72-community:database-mybatis****************************************************** ****************//*!40101 SET NAMES UTF8 * /;/*!40101 SET sql_mode= ' * *;/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 * /;/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * /;/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * /;/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 * /;/*data for the table ' items ' * /Insert   into ' Items '(' id ',' name ',' Price ',' Detail ',' pic ',' Createtime ')Values(1,' Desktops ',3000.0,' The quality of the computer is very good!!! ',NULL,' 2015-02-03 13:22:53 '),(2,' Notebooks ',6000.0,' Good notebook performance, good quality!!!!! ',NULL,' 2015-02-09 13:22:57 '),(3,' backpack ',200.0,' brand-name backpack, high-quality capacity!!! ',NULL,' 2015-02-06 13:23:02 ');/*data for the table ' orderdetail ' * /insert  into  Span class= "hljs-string" > ' OrderDetail '  ( ' id ' ,  orders_id ' ,  ' items_id ' ,  ' items_num ' ) Span class= "Hljs-keyword" >values  (1 , 3 , Span class= "Hljs-number" >1 , 1 ), (2 , 3 , 2 , 3 ), (3 , 4 , 3 , 4 ), (4 , 4 , 2 , 3 ); /*data for the table ' orders ' * /Insert   into ' orders '(' id ',' user_id ',' number ',' Createtime ',' Note ')Values(3,1,' 1000010 ',' 2015-02-04 13:22:35 ',NULL),(4,1,' 1000011 ',' 2015-02-03 13:22:41 ',NULL),(5,Ten,' 1000012 ',' 2015-02-12 16:13:23 ',NULL);/*data for the table ' user ' * /Insert   into ' user '(' id ',' username ',' Birthday ',' Sex ',' Address ')Values(1,' Harry ',NULL,' 2 ',NULL),(Ten,' Zhang San ',' 2014-07-10 ',' 1 ',' Beijing '),( -,' Zhang Xiaoming ',NULL,' 1 ',' Zhengzhou, Henan '),( A,' Chen Xiaoming ',NULL,' 1 ',' Zhengzhou, Henan '),( -,' Zhang San Fung ',NULL,' 1 ',' Zhengzhou, Henan '),( -,' Chen Xiaoming ',NULL,' 1 ',' Zhengzhou, Henan '),( -,' Harry ',NULL,NULL,NULL);/*!40101 SET [email protected]_sql_mode * /;/*!40014 SET [email protected]_foreign_key_checks * /;/*!40014 SET [email protected]_unique_checks * /;/*!40111 SET [email protected]_sql_notes * /;

Author @brianway More articles: personal website | CSDN | Oschina

MyBatis Study Notes (9)-Order commodity Data Model analysis