秒杀系统(一)——数据库设计

用户表:

create table user(
       id bigint PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       username varchar(20) NOT NULL COMMENT '用户名',
       useraccount varchar(20) NOT NULL COMMENT '用户账号',
       password varchar(20) NOT NULL COMMENT '用户密码',
       sex varchar(2) NOT NULL COMMENT '性别',
       age int(5) NOT NULL  COMMENT '年龄',
       address varchar(256) NOT NULL COMMENT '用户地址',
       email varchar(30) NOT NULL COMMENT '用户邮箱'
     
)ENGINE = InnoDB  DEFAULT CHARSET = utf8 COMMENT  = '用户表'

商家表:

create table merchant(
       id bigint PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       merchant_name varchar(20) NOT NULL COMMENT '商家名',
       merchant_account varchar(20) NOT NULL COMMENT '商家账号',
       merchant_password varchar(20) NOT NULL COMMENT '商家密码'
)ENGINE = InnoDB  DEFAULT CHARSET = utf8 COMMENT  = '商家表'

秒杀商品表:

create table msproduct(
       id bigint PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       product_id bigint NOT NULL COMMENT '商品ID',
       ms_title varchar(50) NOT NULL COMMENT '秒杀标题',
       ms_img varchar(50) NOT NULL COMMENT '图片',
       original_price int(20) NOT NULL COMMENT '原价格',
       ms_price  bigint NOT NULL COMMENT '秒杀价格',
       merchant_id int(100) NOT NULL COMMENT '商家ID',
       addition_date datetime NOT NULL COMMENT '添加日期',
       review_date datetime NOT NULL COMMENT '审核日期',
       review_status int(2) NOT NULL COMMENT '状态',
       start_time datetime NOT NULL COMMENT '开始时间',
       end_time datetime NOT NULL COMMENT '结束时间',
       msproduct_number int(10) NOT NULL COMMENT '秒杀商品数',
       stock_number int(10) NOT NULL COMMENT '剩余库存数',
       ms_describe varchar(256) NOT NULL COMMENT  '描述'
       
)ENGINE = InnoDB  DEFAULT CHARSET = utf8 COMMENT  = '秒杀商品表'

秒杀订单表:

create table msorder(
       id bigint PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       product_id bigint NOT NULL COMMENT '商品ID',
       payprice  int(20) NOT NULL COMMENT '支付金额',
       user_id bigint NOT NULL COMMENT '用户ID',
       merchant_id bigint NOT NULL COMMENT '商家ID',
       create_time datetime NOT NULL COMMENT '创建时间',
       pay_time datetime NOT NULL COMMENT '支付时间',
       pay_status int(2) NOT NULL COMMENT '支付状态',
       receiver_address varchar(50) NOT NULL COMMENT '收货地址',
       receiver_phone varchar(11) NOT NULL  COMMENT '收货人电话',
       serialnumber varchar(256) NOT NULL COMMENT  '流水号'
)ENGINE = InnoDB  DEFAULT CHARSET = utf8 COMMENT  = '秒杀订单表'

秒杀商品详细表:

create table msdetail(
       id bigint PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       product_id bigint NOT NULL COMMENT '商品ID',
       merchant_id bigint NOT NULL COMMENT '商家ID',
       product_place varchar(256)  COMMENT '商品产地',
       product_name varchar(256) NOT NULL COMMENT '商品名称',
       brand varchar(32) NOT NULL COMMENT '品牌',
       norms varchar(256) NOT NULL COMMENT '规格',
       detail_img varchar(64) NOT NULL COMMENT '详情图片地址'
       
)ENGINE = InnoDB  DEFAULT CHARSET = utf8 COMMENT  = '秒杀商品详细表'