网易一元夺宝数据库分析（未涉及管理员）

文末有关系模型

/*==============================================================*/
/* Table: Y_address                                             */
/*==============================================================*/
create table Y_address
(
   uid                  int not null comment '用户ID',
   aid                  int not null comment '地址编号',
   aaddress             varchar(1000) not null comment '收获地址',
   aperson              varchar(30) not null comment '收货人',
   aphone               bigint not null comment '收货人电话',
   primary key (uid, aid)
);

alter table Y_address comment '收货地址管理';

/*==============================================================*/
/* Table: Y_buyed                                               */
/*==============================================================*/
create table Y_buyed
(
   bid                  int not null auto_increment comment '购买ID',
   uid                  int not null comment '用户ID',
   sid                  int not null comment '商品ID',
   bnum                 int not null default 1 comment '购买数量（默认为1）',
   saimg01              varbinary(100) comment '晒单图01',
   saimg02              varbinary(100) comment '晒单图02',
   saimg03              varbinary(100) comment '晒单图03',
   btime                datetime not null comment '购买时间',
   primary key (bid)
);

alter table Y_buyed comment '全价购买表
';

/*==============================================================*/
/* Table: Y_buyedappraise                                       */
/*==============================================================*/
create table Y_buyedappraise
(
   bpid                 int not null auto_increment comment '评论ID',
   uid                  int not null comment '用户ID',
   sid                  int not null comment '商品ID',
   bpcontent            varchar(1000) not null comment '评论内容',
   bptime               datetime not null comment '评论时间',
   primary key (bpid)
);

alter table Y_buyedappraise comment '全价商品购买评价';

/*==============================================================*/
/* Table: Y_duobao                                              */
/*==============================================================*/
create table Y_duobao
(
   dduonum              bigint not null auto_increment comment '夺宝号码（‘前台使用时在次号码前拼接100或1000’）',
   uid                  int not null comment '用户id',
   sid                  int not null comment '商品id',
   dlasttime            datetime not null comment '最后一次夺宝时间',
   dstaic               tinyint not null default 0,
   primary key (dduonum)
);

alter table Y_duobao comment '夺宝记录表';

/*==============================================================*/
/* Table: Y_rechare                                             */
/*==============================================================*/
create table Y_rechare
(
   rid                  int not null auto_increment comment '充值ID',
   uid                  int not null comment '用户ID',
   rmoney               int not null comment '充值金额',
   rtime                datetime not null comment '充值时间',
   primary key (rid)
);

alter table Y_rechare comment '用户充值';

/*==============================================================*/
/* Table: Y_shop                                                */
/*==============================================================*/
create table Y_shop
(
   sid                  int not null auto_increment comment '商品ID
            ',
   sname                varchar(100) not null comment '商品名',
   svid                 int not null comment '商品种类ID',
   sprice               int not null comment '商品总价格（最小单位/元）',
   ssingleprice         tinyint default 1 comment '夺宝单价（1元/人；10元/人，默认是一元夺宝）',
   smainimg             varchar(100) not null comment '正面商品图',
   sjieshaoimg01        varchar(100) not null comment '商品介绍图第一张',
   sjieshaoimg02        varchar(100) not null comment '商品介绍图第二张',
   sjieshaoimg03        varchar(100) comment '商品介绍图第三张',
   sjieshaoimg04        varchar(100) comment '商品介绍图第四张',
   sjieshao             varchar(300) comment '商品介绍',
   ssave                int not null default 200 comment '商品库存（默认200）',
   sstaic               tinyint not null default 1 comment '是否下架（默认为1，未下架）',
   ssavetime            datetime not null comment '入库时间',
   primary key (sid)
);

alter table Y_shop comment '商品信息表';

/*==============================================================*/
/* Table: Y_shopappraise                                        */
/*==============================================================*/
create table Y_shopappraise
(
   pid                  int not null auto_increment comment '评论编号',
   luckynum             int not null comment '幸运号码',
   pcontent             varchar(1000) comment '评价',
   saimg01              varbinary(100) comment '晒单图01',
   saimg02              varbinary(100) comment '晒单图02',
   saimg03              varbinary(100) comment '晒单图03',
   ptime                datetime not null comment '评价时间',
   primary key (pid)
);

alter table Y_shopappraise comment '商品评价
';

/*==============================================================*/
/* Table: Y_shoppingcar                                         */
/*==============================================================*/
create table Y_shoppingcar
(
   uid                  int not null comment '用户ID',
   sid                  int not null comment '商品ID',
   snum                 int not null default 1 comment '购买数量（默认为1）',
   sctime               datetime not null comment '加入购物车时间',
   primary key (uid, sid)
);

alter table Y_shoppingcar comment '购物车(仅针对全价购买商品，夺宝清单参照duobao表)';

/*==============================================================*/
/* Table: Y_shopvariety                                         */
/*==============================================================*/
create table Y_shopvariety
(
   svid                 int not null comment '商品种类ID',
   svmiaoshu            varchar(60) not null comment '种类描述',
   svnote               varchar(600) comment '备注',
   primary key (svid)
);

alter table Y_shopvariety comment '商品种类
';

/*==============================================================*/
/* Table: Y_showbuyed                                           */
/*==============================================================*/
create table Y_showbuyed
(
   luckynum             int not null comment '幸运号',
   swtime               datetime not null comment '夺宝成功的时间(或者说是揭晓时间)',
   primary key (luckynum)
);

alter table Y_showbuyed comment '晒单（夺宝成功商品）';

/*==============================================================*/
/* Table: Y_user                                                */
/*==============================================================*/
create table Y_user
(
   uid                  int not null auto_increment comment '用户ID',
   uname                varchar(30) comment '用户的真实姓名',
   ushowname            varchar(200) comment '用户昵称',
   uIDnumber            varchar(18) comment '身份证号码',
   uemail               varchar(100) not null comment '用户邮箱',
   uphone               bigint not null comment '手机号码',
   upassword            varchar(32) not null comment '用户密码',
   utouxiang            varchar(100) not null comment '用户头像',
   umoney               bigint not null default 100 comment '用户初始余额（100元）',
   ucoupon              int default 5 comment '用户优惠券（抵用现金/元,初始为5元）',
   ubaoshi              int default 20 comment '用户宝石（用于兑换商品/个，初始为20个）',
   uregtiem             datetime not null comment '用户注册时间',
   ustaic               tinyint not null default 1 comment '用户当前状态（默认为“1”，有效用户）',
   primary key (uid),
   unique key AK_Key_2 (uIDnumber),
   unique key AK_Key_3 (uphone),
   unique key AK_Key_4 (uemail)
);

alter table Y_user comment '用户表
';

alter table Y_address add constraint FK_AU foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_buyed add constraint FK_BS foreign key (sid)
      references Y_shop (sid) on delete restrict on update restrict;

alter table Y_buyed add constraint FK_BU foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_buyedappraise add constraint FK_BP_S foreign key (sid)
      references Y_shop (sid) on delete restrict on update restrict;

alter table Y_buyedappraise add constraint FK_BP_U foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_duobao add constraint FK_Lucky_S foreign key (sid)
      references Y_shop (sid) on delete restrict on update restrict;

alter table Y_duobao add constraint FK_Lucky_U foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_rechare add constraint FK_RU foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_shop add constraint FK_SV foreign key (svid)
      references Y_shopvariety (svid) on delete restrict on update restrict;

alter table Y_shopappraise add constraint FK_PD foreign key (luckynum)
      references Y_duobao (dduonum) on delete restrict on update restrict;

alter table Y_shoppingcar add constraint FK_CS foreign key (sid)
      references Y_shop (sid) on delete restrict on update restrict;

alter table Y_shoppingcar add constraint FK_UC foreign key (uid)
      references Y_user (uid) on delete restrict on update restrict;

alter table Y_showbuyed add constraint FK_Lucky_D foreign key (luckynum)
      references Y_duobao (dduonum) on delete restrict on update restrict;

 
 

  
  ##更改：建立期号表
create table Y_qihao
(
   qnum                 int  comment '期号',
   sid                  int   comment '商品ID',
   qtime                 datetime comment '该期开始时间',
   qlasttime                datetime comment '该期结束时间',
   primary key (qnum,qtime)
);

 
 
 
 

  
  alter table y_duobao add constraint FK_DQ foreign key (dqihao)
      references Y_qihao (qnum) on delete restrict on update restrict;
ALTER TABLE y_qihao ADD CONSTRAINT FK_QS FOREIGN KEY (sid)
      REFERENCES Y_shop (sid) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
 

关系模型图：