Big Data 241 - E-commerce Core Transaction Data Model

Business Requirements

Most critical business in e-commerce system, e-commerce operations activities all revolve around this theme. Selected metrics include: order count, product count, payment amount, analyze these metrics by sales region, product type.

Requirement Segments

E-commerce platform core transactions can be divided into several main stages:

• Product browsing: User behavior data of browsing products
• Add to cart: User behavior of adding products to shopping cart
• Order placement: Order generation behavior completed by user on e-commerce platform
• Payment: Payment is crucial in transactions
• Shipping: Shipping data records merchant shipping time, logistics company, tracking number, etc.
• Receipt and evaluation: User evaluation after receiving products, return/exchange behaviors, etc.

Core Business Process

• Product browsing
• Add to cart
• Order placement
• Payment
• Shipping
• Receipt and evaluation

Business Database Table Structure

1. Trade Orders Table (wzk_trade_orders)

CREATE TABLE `wzk_trade_orders` (
  `orderId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
  `orderNo` varchar(20) NOT NULL COMMENT 'Order Number',
  `userId` bigint(11) NOT NULL COMMENT 'User ID',
  `status` tinyint(4) NOT NULL DEFAULT '-2' COMMENT 'Order Status',
  `productMoney` decimal(11,2) NOT NULL COMMENT 'Product Amount',
  `totalMoney` decimal(11,2) NOT NULL COMMENT 'Order Amount (including shipping)',
  `payMethod` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Payment Method',
  `isPay` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Whether Paid',
  `areaId` int(11) NOT NULL COMMENT 'Lowest Level Area',
  `createTime` varchar(25) NOT NULL COMMENT 'Order Time',
  `payTime` varchar(25) DEFAULT NULL COMMENT 'Payment Time',
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=355 DEFAULT CHARSET=utf8;

2. Order Product Table (wzk_order_product)

CREATE TABLE `wzk_order_product` (
  `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
  `orderId` BIGINT(11) NOT NULL COMMENT 'Order ID',
  `productId` BIGINT(11) NOT NULL COMMENT 'Product ID',
  `productNum` BIGINT(11) NOT NULL DEFAULT '0' COMMENT 'Product Quantity',
  `productPrice` DECIMAL(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Product Price',
  `money` DECIMAL(11,2) DEFAULT '0.00' COMMENT 'Payment Amount',
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`),
  KEY `goodsId` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=1260 DEFAULT CHARSET=utf8;

3. Product Info Table (wzk_product_info)

CREATE TABLE `wzk_product_info` (
  `productId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Product ID',
  `productName` varchar(200) NOT NULL COMMENT 'Product Name',
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;