Analysis and construction of classic e-commerce database (I.)

Analysis and design of traditional e-commerce tables (simple version, including business and user relationship processing only)

Standing at the user's angle

1. First, there is a user table, including the user's basic information.
2. Then there is a commodity table, including the basic information of the commodity, in addition, there should be a merchant's ID as a foreign key, can help users understand the product belongs to.
3. Then the order form, an order can have multiple items, each product should have the user purchase quantity and unit price as well as the total price. An order should also include the total price of the order, the order number, and so on. Therefore, a piece of data cannot represent this information.
4. So to have an intermediate table, we call it the Order Item table, which represents the information for each item in the order.
5. The order item includes ID, item ID, purchase quantity, single Total price, order ID, user ID.
6. The order includes: ID, order date, order status, Total price.

Stand in the merchant's angle

1. First, the merchant table, including the basic information.
2. For the product, just include the basic information. Same. You need to add the merchant ID to indicate the owning.
3. For orders, the merchant should be able to check the number of each item sold and the total price, the above design and can be satisfied.
4. Also, businesses need to count orders. See which users purchased the goods, and how many they purchased. The above design can also be met.

Design Table

1. Customer (user table): CID, CNAME, Cphone, caddress
2. Merchant (merchant table): Mid, Mname, Mphone, maddress
3. Product (commodity table):p ID, pname, Pprice, Premain, mid
4. Order (Orders table): OID, Odatetime, Ostate, Osummoney, CID
5. OrderItem (Order Entry table): Oiid, Iobuynum, Iosummoney, PID, OID

Build table

SET foreign_key_checks=0;--------------------------------Table structure for ' customer '--------------------------- ---DROP TABLE IF EXISTS ' customer ';  CREATE TABLE ' customer ' (' cid ' int (one) not null auto_increment, ' cname ' varchar ') default NULL, ' cphone ' varchar (11) Default NULL, ' caddress ' varchar ($) default NULL, PRIMARY KEY (' CID ')) engine=innodb default Charset=utf8;--------- -----------------------table structure for ' merchant '------------------------------DROP table IF EXISTS ' merchant ';  CREATE TABLE ' Merchant ' (' Mid ' int (one) not null auto_increment, ' mname ' varchar () default NULL, ' mphone ' varchar (11) Default NULL, ' maddress ' varchar ($) default NULL, PRIMARY KEY (' mid ')) Engine=innodb default Charset=utf8;--------- -----------------------table structure for ' orders '------------------------------DROP Table IF EXISTS ' orders '; CREATE TABLE ' orders ' (' oid ' int (one) not null auto_increment, ' odatetime ' datetime default NULL, ' ostate ' varchar (20) DefaultNULL, ' Osummoney ' double default null, ' CID ' int (one) not NULL, PRIMARY key (' oid '), key ' CID ' (' CID '), CONSTRAINT ' O Rder_ibfk_1 ' FOREIGN KEY (' cid ') REFERENCES ' customer ' (' CID ')) Engine=innodb DEFAULT Charset=utf8;-------------------- ------------table structure for ' ordersitem '------------------------------DROP table IF EXISTS ' Ordersitem '; CREATE TABLE ' Ordersitem ' (' oiid ' int (one) not null auto_increment, ' iobuynum ' int (one) default NULL, ' Iosummoney ' Doubl E default NULL, ' pid ' int (one) not null, ' oid ' int (one) not NULL, PRIMARY key (' oiid '), key ' pid ' (' pid '), Key ' OID ' ( ' OID '), CONSTRAINT ' ordersitem_ibfk_2 ' FOREIGN KEY (' oid ') REFERENCES ' orders ' (' oid '), CONSTRAINT ' Ordersitem_ibfk_1 ' F Oreign KEY (' pid ') REFERENCES ' product ' (' pid ') engine=innodb DEFAULT Charset=utf8;-------------------------------- Table structure for ' product '------------------------------DROP table IF EXISTS ' product '; CREATE TABLE ' product ' (' pid ' int (one) not NULL auto_increment, ' pname ') varchar () default NULL, ' Pprice ' double default null, ' Premain ' int (one) default null, ' mid ' int (one) not NULL, PRIMA RY key (' pid '), Key ' mid ' (' mid '), CONSTRAINT ' Product_ibfk_1 ' FOREIGN KEY (' mid ') REFERENCES ' Merchant ' (' mid ')) Engin E=innodb DEFAULT Charset=utf8;

Insert statement

2. Customer

   insert into customer values(null,‘张三‘,‘15639854081‘,‘南阳‘);insert into customer values(null,‘李四‘,‘17439804082‘,‘上海‘);insert into customer values(null,‘王五‘,‘13438454088‘,‘长春‘);insert into customer values(null,‘赵六‘,‘18239454061‘,‘上海‘);insert into customer values(null,‘刘八‘,‘15439854382‘,‘北京‘);insert into customer values(null,‘旺财‘,‘17433854087‘,‘广东‘);

4. Business

   insert into merchant values(null,‘广州宝洁‘,‘800-400-300‘,‘广州‘),(null,‘北京老板‘,‘808-300-446‘,‘北京‘),(null,‘郑州宇通‘,‘888-400-533‘,‘郑州‘);

6. Products

   insert into product values(null,‘佳洁士炫白牙齿牙膏‘,6.8,300,1),(null,‘舒肤佳‘,4.5,400,1),(null,‘飘柔‘,20.8,5,1),(null,‘海飞丝‘,32.5,20,1);insert into product values(null,‘老板油烟机‘,566.00,40,2),(null,‘老板插座‘,56.00,400,2),(null,‘老板风扇‘,128.00,360,2),(null,‘老板压力锅‘,288.00,460,2);insert into product values(null,‘宇通油电混合客车‘,200000,10,3),(null,‘宇通电动客车‘,120000,20,3),(null,‘宇通重卡‘,400000,60,3);     

8. Order

   insert into orders values(1,‘2016-03-02 12:30:02‘,‘未支付‘,0,1);insert into orders values(2,‘2016-02-02 09:30:02‘,‘已支付‘,0,3);insert into orders values(3,‘2016-01-02 12:28:02‘,‘已支付‘,0,5);insert into orders values(4,‘2016-01-02 12:28:02‘,‘已支付‘,0,4);

10. Order Items

    --订单1：有三件商品insert into ordersitem values(null,3,20.40,1,1),(null,2,41.6,3,1),(null,1,400000,11,1);--订单2：有两件商品insert into ordersitem values(null,1,566,5,2),(null,1,128,7,2);--订单3：有一件商品insert into ordersitem values(null,1,120000,10,3);--订单4：有三件商品insert into ordersitem values(null,3,20.40,1,4),(null,1,6.8,1,4),(null,1,400000,11,4);

Task

1. Check the product name and quantity from the commodity table starting with the "boss" two words, and sort by quantity in descending order (user search)
2. Check all the product name, price, Manufacturer, origin) (Product information Display)
3. From the order form, search for the product name, unit price, quantity, customer name and order date for all items with the purchase order number "2" (User View order)
4. Update Order 1: The update status is paid and the total price is the actual price. Display: Product name, unit price, quantity, customer name and order date, Total price, order status for all items with order number "1" (User pays to view order)
5. Delete Order 3 Information (user cancels order)
6. The number of goods sold, the first name, the name of the purchaser (the Merchant to view the sale of a product)
7. Check the sales of Procter and Gamble, sorted by the number of sales from high to low (merchant view sold data)
8. Sort the items sold according to the quantity sold from highest to lowest (admin view sold data)

Answer

1. SELECT pname trade name, Premain remainder from product WHERE pname like ' boss% ' ORDER by Premain; 2. SELECT pname trade name, Premain remaining quantity, Pprice price, mname manufacturer, maddress origin from product, merchant WHERE Product.mid = Merch Ant.mid; 3.SELECT pname trade name, Premain remainder, pprice price, CNAME customer name, odatetime order date from product, customer, orders, O Rdersitem WHERE ordersitem.pid = product.pid and ordersitem.oid = orders.oid and orders.cid = Customer.cid and order S.oid = 2; 4.UPDATE orders SET orders.ostate = ' paid ', Orders.osummoney = (select (Select SUM (Order Sitem.iobuynum * Ordersitem.iosummoney) from Ordersitem WHERE ordersitem.oid = orders.oid and Ord Ersitem.oid = 1 GROUP by ordersitem.oid)) WHERE orders.oid = 1; Reference: http://huangyunbin.iteye.com/blog/1190882 5.   Atomic Operation DELETE from Ordersitem where oid=3;delete from orders where oid=3;6. SELECT sum (ordersitem.iobuynum) sold quantity, sum (ordersitem.iobuynum*product.pprice) Total Price, product.pnameProduct name, Customer.cname customer name from product, customer, orders, Ordersitem WHERE ordersitem.pid = Product.pid and Ordersite  M.oid = orders.oid and orders.cid = customer.cid and product.pid = 1 GROUP by customer.cid;7. SELECT sum (ordersitem.iobuynum) sold quantity, sum (Ordersitem.iobuynum * product.pprice) Total price, Product.pname product name from Pro Duct, Ordersitem, merchant WHERE ordersitem.pid = product.pid and Product.mid = Merchant.mid and merchant.mname = "wide State Procter and Gamble "GROUP by Product.pname; 8. SELECT sum (ordersitem.iobuynum) sold quantity, sum (Ordersitem.iobuynum * product.pprice) Total price, Product.pname product name from Pro Duct, Ordersitem, merchant WHERE ordersitem.pid = product.pid and Product.mid = Merchant.mid GROUP by Product.pname OR DER by Ordersitem. ' Iobuynum ' DESC;

Attached: All operating source code

/*navicat MySQL Data transfersource server:123source Server version:50027source host:localhost:330 6Source database:mygisttarget Server type:mysqltarget server Version:50027file encoding:65001date : 2016-03-16 20:00:31*/set foreign_key_checks=0;--------------------------------Table structure for ' customer '------ ------------------------DROP TABLE IF EXISTS ' customer ';  CREATE TABLE ' customer ' (' cid ' int (one) not null auto_increment, ' cname ' varchar ') default NULL, ' cphone ' varchar (11) Default NULL, ' caddress ' varchar ($) default NULL, PRIMARY KEY (' CID ')) engine=innodb default Charset=utf8;--------- -----------------------Records of customer------------------------------INSERT into ' Customer ' VALUES (' 1 ', ' Zhang San ', ' 15639854081 ', ' Nanyang '); insert INTO ' Customer ' values (' 2 ', ' John Doe ', ' 17439804082 ', ' Shanghai '); INSERT INTO ' Customer ' values (' 3 ', ' Harry ', ' 13438454088 ', ' Changchun '); insert INTO ' Customer ' VALUES (' 4 ', ' Zhao Liu ', ' 18239454061 ', ' Shanghai '); INSERT into ' Customer ' VALUES (' 5 ', ' Liu Ba ', ' 15439854382 ', ' Beijing '); INSERT into ' Customer ' values (' 6 ', ' Wang Choi ', ' 17433854087 ', ' Guangdong ');------ --------------------------table structure for ' merchant '------------------------------DROP table IF EXISTS ' merchant ' ;  CREATE TABLE ' Merchant ' (' Mid ' int (one) not null auto_increment, ' mname ' varchar () default NULL, ' mphone ' varchar (11) Default NULL, ' maddress ' varchar ($) default NULL, PRIMARY KEY (' mid ')) Engine=innodb default Charset=utf8;--------- -----------------------Records of merchant------------------------------INSERT into ' Merchant ' VALUES (' 1 ', ' Guangzhou Procter and Gamble ', ' 800-400-300 ', ' Guangzhou '); insert INTO ' Merchant ' values (' 2 ', ' Beijing boss ', ' 808-300-446 ', ' Beijing '); insert INTO ' Merchant ' values (' 3 ', ' Zhengzhou Yutong ', ' 888-400-533 ', ' Zhengzhou ');--------------------------------Table structure for ' orders '--------------------------- ---DROP TABLE IF EXISTS ' orders '; CREATE TABLE ' orders ' (' oid ' int (one) not null auto_increment, ' odatetime ' datetime default NULL, ' ostate ' varchar (20)Default NULL, ' Osummoney ' double default null, ' CID ' int (one) not NULL, PRIMARY key (' oid '), key ' CID ' (' CID '), CONST Raint ' Order_ibfk_1 ' FOREIGN KEY (' cid ') REFERENCES ' customer ' (' CID ')) Engine=innodb DEFAULT Charset=utf8;------------- -------------------Records of orders------------------------------INSERT into ' orders ' VALUES (' 1 ', ' 2016-03-02  12:30:02 ', ' paid ', ' 400144.4 ', ' 1 '); insert INTO ' orders ' VALUES (' 2 ', ' 2016-02-02 09:30:02 ', ' paid ', ' 694 ', ' 3 '); INSERT into ' Orders ' VALUES (' 4 ', ' 2016-01-02 12:28:02 ', ' paid ', ' 0 ', ' 4 ');--------------------------------Table structure for ' Orde Rsitem '------------------------------DROP TABLE IF EXISTS ' Ordersitem '; CREATE TABLE ' Ordersitem ' (' oiid ' int (one) not null auto_increment, ' iobuynum ' int (one) default NULL, ' Iosummoney ' Doubl E default NULL, ' pid ' int (one) not null, ' oid ' int (one) not NULL, PRIMARY key (' oiid '), key ' pid ' (' pid '), Key ' OID ' ( ' OID '), CONSTRAINT ' Ordersitem_ibfk_1 ' FOREIGN KEY (' pid ') REFERENCES ' product ' ('PID '), CONSTRAINT ' ordersitem_ibfk_2 ' FOREIGN KEY (' oid ') REFERENCES ' orders ' (' oid ')) Engine=innodb DEFAULT Charset=utf8 ;--------------------------------Records of Ordersitem------------------------------INSERT into ' Ordersitem ' VALUES (' 1 ', ' 3 ', ' 20.4 ', ' 1 ', ' 1 '); insert INTO ' ordersitem ' values (' 2 ', ' 2 ', ' 41.6 ', ' 3 ', ' 1 '); insert INTO ' ordersitem ' values (' 3 ', ' 1 ', ' 400000 ', ' One ', ' 1 '); insert INTO ' Ordersitem ' VALUES (' 4 ', ' 1 ', ' 566 ', ' 5 ', ' 2 '); insert INTO ' Ordersitem ' VALUE  S (' 5 ', ' 1 ', ' n ', ' 7 ', ' 2 '); insert INTO ' ordersitem ' values (' 7 ', ' 3 ', ' 20.4 ', ' 1 ', ' 4 '); insert INTO ' ordersitem ' values (' 8 ', ' 1 ', ' 6.8 ', ' 1 ', ' 4 '); INSERT into ' Ordersitem ' VALUES (' 9 ', ' 1 ', ' 400000 ', ' One ', ' 4 ');---------------------------- ----table structure for ' product '------------------------------DROP table IF EXISTS ' product '; CREATE TABLE ' product ' (' pid ' int (one) not null auto_increment, ' pname ' varchar () default NULL, ' Pprice ' double Defau Lt null, ' premain ' int (one) default NULL, ' Mid ' int (One) not NULL, PRIMARY key (' pid '), Key ' mid ' (' mid '), CONSTRAINT ' Product_ibfk_1 ' FOREIGN KEY (' mid ') REFERENCES ' Merc Hant ' (' mid ')) Engine=innodb DEFAULT Charset=utf8;--------------------------------Records of product----------------  --------------INSERT INTO ' product ' values (' 1 ', ' Crest white tooth toothpaste ', ' 6.8 ', ' + ', ' 1 '); INSERT INTO ' product ' values (' 2 ', ' soothing ', ' 4.5 ', ' + ', ' 1 '); INSERT INTO ' product ' values (' 3 ', ' rejoice ', ' 20.8 ', ' 5 ', ' 1 '); INSERT INTO ' product ' values (' 4 ', ' Sea Fly ', '  32.5 ', ' + ', ' 1 '); INSERT INTO ' product ' values (' 5 ', ' Boss hood ', ' 566 ', ' + ', ' 2 '); INSERT INTO ' product ' values (' 6 ', ' Boss sockets ', ' + ', ' + ', ' 2 '); INSERT INTO ' product ' values (' 7 ', ' Boss fan ', ' + ', ' n ', ' 2 '); INSERT INTO ' product ' values (' 8 ', ' Boss ' pressure cooker ', ' 288 ', ' 460 ', ' 2 '); INSERT INTO ' product ' values (' 9 ', ' Yutong electric hybrid bus ', ' 200000 ', ' 3 '); INSERT INTO ' product ' values (' 1 0 ', ' yutong Electric bus ', ' 120000 ', ' 3 ', ' + '); INSERT into ' Product ' VALUES (' 11 ', ' Yutong heavy card ', ' 400000 ', ' 60 ', ' 3 ');

Classic e-commerce database analysis and Construction (i)