Realtime Warehouse - Business Database Table Structure: T...

Definition of Realtime Data Warehouse

Realtime data warehouse is a data warehouse system that differs from traditional batch processing data warehouses by emphasizing low latency, high throughput, and high availability. It can process streaming data or near-real-time data streams, enabling enterprises to timely monitor key metrics and make decisions.

Key Features of Realtime Data Warehouse

Low-latency data processing: The time from data collection to queryable analysis is extremely short, typically in seconds or milliseconds.
High-concurrency query support: Can support multiple users querying simultaneously to meet business needs.
High availability and reliability: System remains stable under high load, supports data redundancy and fault tolerance mechanisms.
Unified batch and stream processing: Supports both real-time stream computing and batch data analysis, meeting diverse needs.
Scalability: Can dynamically expand storage and computing resources through distributed architecture to meet growing business needs.

Development Trends of Realtime Data Warehouse

Cloud-native realtime data warehouse: More enterprises choose to build realtime data warehouses on cloud platforms to improve resource management and cost control.
Lakehouse integration: Combines advantages of data lake and data warehouse to support unified analysis of structured and unstructured data.
AI and machine learning integration: Realtime data warehouse will deeply integrate with AI and machine learning to achieve intelligent analysis and prediction.
Open source ecosystem development: More open source tools continuously emerge, lowering the threshold for enterprises to build realtime data warehouses.

Business Database Table Structure

The business database includes the following tables:

Trade orders table (trade_orders)
Order products table (order_product)
Product information table (product_info)
Product category table (product_category)
Merchant store table (shops)
Merchant regional organization table (shop_admin_org)
Payment method table (payments)

Trade Orders Table

CREATE TABLE `wzk_trade_orders` (
  `orderId` bigint(11) NOT NULL DEFAULT 0 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 -3: user rejected -2: unpaid -1: user cancelled 0: pending shipment 1: in delivery 2: user confirmed receipt',
  `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 0: unknown 1: Alipay 2: WeChat 3: Cash 4: Other',
  `isPay` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Payment status 0: unpaid 1: paid',
  `areaId` int(11) NOT NULL COMMENT 'Lowest level area',
  `tradeSrc` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Order source 0: mall 1: WeChat 2: mobile version 3: Android App 4: iOS App',
  `tradeType` int(11) DEFAULT 0 COMMENT 'Order type',
  `isRefund` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Refund status 0: no 1: yes',
  `dataFlag` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Order valid flag -1: deleted 1: valid',
  `createTime` varchar(25) NOT NULL COMMENT 'Order creation time',
  `payTime` varchar(25) DEFAULT NULL COMMENT 'Payment time',
  `modifiedTime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Order update time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records order information including order status, creation time, payment time, update time, etc.

Order Products Table

CREATE TABLE `wzk_order_product` (
  `id` bigint(11) NOT NULL DEFAULT 0,
  `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',
  `extra` text DEFAULT NULL COMMENT 'Extra information',
  `createTime` varchar(25) DEFAULT NULL COMMENT 'Creation time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records information about products purchased in orders, including product quantity, unit price, etc.

Product Information Table

CREATE TABLE `wzk_product_info` (
  `productId` bigint(11) NOT NULL DEFAULT 0 COMMENT 'Product ID',
  `productName` varchar(200) NOT NULL COMMENT 'Product name',
  `shopId` bigint(11) NOT NULL COMMENT 'Store ID',
  `price` decimal(11,2) NOT NULL DEFAULT 0.00 COMMENT 'Store price',
  `isSale` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'On sale status 0: not for sale 1: for sale',
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'New product status 0: no 1: yes',
  `categoryId` int(11) NOT NULL COMMENT 'goodsCatId Last level product category ID',
  `createTime` varchar(25) NOT NULL,
  `modifyTime` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Modification time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records detailed product information, corresponding merchant ID, product attributes (whether new, whether on shelf), including creation time and modification time.

Product Category Table

CREATE TABLE `wzk_product_category` (
  `catId` int(11) NOT NULL DEFAULT 0 COMMENT 'Category ID',
  `parentId` int(11) NOT NULL COMMENT 'Parent ID',
  `catName` varchar(20) NOT NULL COMMENT 'Category name',
  `isShow` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Display status 0: hidden 1: displayed',
  `sortNum` int(11) NOT NULL DEFAULT 0 COMMENT 'Sort number',
  `isDel` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Delete flag 1: valid -1: deleted',
  `createTime` varchar(25) NOT NULL COMMENT 'Creation time',
  `level` tinyint(4) DEFAULT 0 COMMENT 'Category level, total 3 levels'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Product category table, divided into 3 levels.

Merchant Store Table

CREATE TABLE `wzk_shops` (
  `shopId` int(11) NOT NULL DEFAULT 0 COMMENT 'Store ID, auto-increment',
  `userId` int(11) NOT NULL COMMENT 'Store contact ID',
  `areaId` int(11) DEFAULT 0,
  `shopName` varchar(100) DEFAULT '' COMMENT 'Store name',
  `shopLevel` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Store level',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Store status',
  `createTime` date DEFAULT NULL,
  `modifyTime` datetime DEFAULT NULL COMMENT 'Modification time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records detailed store information.

Merchant Regional Organization Table

CREATE TABLE `wzk_shop_admin_org` (
  `id` int(11) NOT NULL DEFAULT 0 COMMENT 'Organization ID',
  `parentId` int(11) NOT NULL COMMENT 'Parent ID',
  `orgName` varchar(100) NOT NULL COMMENT 'Organization name',
  `orgLevel` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Organization level 1: HQ and regional level departments; 2: various departments under HQ and base departments; 3: specific work departments',
  `isDelete` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Delete flag 1: deleted 0: valid',
  `createTime` varchar(25) DEFAULT NULL COMMENT 'Creation time',
  `updateTime` varchar(25) DEFAULT NULL COMMENT 'Last modification time',
  `isShow` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Display status 0: yes 1: no',
  `orgType` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Organization type 0: CEO office; 1: R&D; 2: Sales; 3: Operations; 4: Product'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records the region to which the store belongs.

Payment Method Table

CREATE TABLE `wzk_payments` (
  `id` int(11) NOT NULL,
  `payMethod` varchar(20) DEFAULT NULL,
  `payName` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `payOrder` int(11) DEFAULT 0,
  `online` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Note: Records payment methods.