ylbtech-dbs:ylbtech-dbs-m-YinTai(银泰网) |
-- =============================================-- DatabaseName:YinTai-- desc:银泰网(电子商务)-- pubdate:09:52 2014-07-18-- author:ylbtech-- http://m.yintai.com-- =============================================
1.A,数据库关系图(Database Diagram) 返回顶部 |
1.B,数据库设计脚本(Database Design Script)返回顶部 |
1.B.1,
1.B.1.1, sql-basic.sql
use mastergo-- =============================================-- DatabaseName:银泰网-- pubdate:09:52 2014-07-18-- author:ylbtech-- http://m.yintai.com/-- =============================================IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'YinTai') DROP DATABASE YinTaiGOCREATE DATABASE YinTaiGOuse YinTaigo-- =============================================-- table:账户-- 只做电子邮箱验证注册-- =============================================create table Account(accountId int identity(100001,1) PRimary key, --编号【PK】nickname varchar(20) not null, --昵称email varchar(200), --E-Mailphone varchar(200), --手机号[passWord] varchar(20) not null, --密码[type] int, --类型 0:手机号;1:电子邮箱validEmail bit, --验证validPhone bit, --验证pubdate datetime default(getdate()), --注册时间[enable] bit --状态 0:正常;1:禁用)goinsert into Account(nickname,email,phone,[password],[type],validEmail,validPhone,pubdate,[enable])values('750833968','750833968@QQ.com','','123456',1,0,0,getdate(),0)go-- =============================================-- table:用户中心[积分、银元]-- =============================================--create table UserCenter--(--)go--drop table Address-- =============================================-- table:地址-- =============================================create table [Address](addressId int primary key identity(1000,1), --编号【PK,ID】fullName varchar(200), --收货人mobile varchar(40), --联系电话province varchar(40), --省city varchar(40), --市area varchar(40), --区县addressDetail varchar(200), --详细地址accountId int references Account(accountId), --账号ID【FK】)goinsert into [Address](fullName,mobile,province,city,area,addressDetail,accountId)values('rain','123434546456','beijing','beijing','haidian','long range',100001)insert into [Address](fullName,mobile,province,city,area,addressDetail,accountId)values('rain','123434546456','beijing','beijing','haidian','long range',100001)-- ---------公共表------------------------------go-- =============================================-- table:组表(大分类)-男装、女装-- =============================================create table [Group](groupName varchar(200) primary key --组名)insert into [Group](groupName) values('男装')insert into [Group](groupName) values('女装')go-- =============================================-- table:分类 男装、女装-- =============================================create table Category(categoryId int primary key identity(101,1), --编号[name] varchar(200), --分类名称searchCondition int, --查找条件parentId int, --0:父;其他:父级别条件,即searchCondition[type] int --0:文本;1:超链接(仅限二级使用))insert into Category([name],searchCondition,parentId,[type]) values('女装',100,0,0)insert into Category([name],searchCondition,parentId,[type]) values('男装',101,0,0)goinsert into Category([name],searchCondition,parentId,[type]) values('上装',10101,101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('POLO衫',-1,10101,0)insert into Category([name],searchCondition,parentId,[type]) values('背心',-1,10101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('套装',10102,101,0)insert into Category([name],searchCondition,parentId,[type]) values('下装',10103,101,0)goinsert into Category([name],searchCondition,parentId,[type]) values('美妆',102,0,0)insert into Category([name],searchCondition,parentId,[type]) values('家居',103,0,0)insert into Category([name],searchCondition,parentId,[type]) values('运动',104,0,0)go-- =============================================-- table:品牌 PeaceBird、LEE-- =============================================create table Brand(brandId int identity(10001,1), --编号【PK,ID】[name] varchar(200) primary key, --名称searchCondition int, --查找条件categoryId int --类别ID)goinsert into Brand([name],searchCondition,categoryId) values('Peace Bird',100,101)insert into Brand([name],searchCondition,categoryId) values('Lee',100,101)go-- =============================================-- table:反馈-- =============================================create table Feedback(feedbackId int primary key identity(1000,1), --编号【PK,ID】phoneModel varchar(200), --电话型号contact varchar(200), --联系方式content varchar(200), --意见network varchar(200), --网络信息browser varchar(200), --浏览器questionType varchar(200), --问题类型pubdate datetime default(getdate()), --时间[type] int, --类型[status] int, --状态replyContent varchar(200), --回复内容replyPubdate datetime, --回复日期accountType int, --用户类型accountId int foreign key references Account(accountId) --账户ID【FK】 )go--drop table Recommend-- =============================================-- table:应用推荐-- =============================================create table Recommend(recommendId int primary key identity(100,1), --编号【PK,ID】[name] varchar(200), --应用名称introduce varchar(700), --说明downloadUrl varchar(500), --下载地址icon varchar(500), --图标[type] int --类型)goinsert into Recommend([name],introduce,downloadUrl,icon,[type])values('','','https://itunes.apple.com/cn/app/tian-qi-yi-bao/id663183019?mt=8','http://yrs.yintai.com/rs/img/AppCMS/20131209162750_tianqi.jpg',1)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('','','https://itunes.apple.com/cn/app/tian-qi-yi-bao/id663183019?mt=8','http://yrs.yintai.com/rs/img/AppCMS/20131209162750_tianqi.jpg',1)goinsert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀画报(安卓版)','教你扮靓,女生必备!','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀画报(安卓版)','教你扮靓,女生必备!','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)insert into Recommend([name],introduce,downloadUrl,icon,[type])values('哈秀画报(安卓版)','教你扮靓,女生必备!','http://www.haxiu.com/azapp/download_android.php?form=8','http://yrs.yintai.com/rs/img/AppCMS/20131209153301_114haxu.jpg',2)go-- drop table Activity-- =============================================-- table:限时特卖-- =============================================create table Activity([id] int, --编号【FK】title varchar(200), --标题imageUrl varchar(500), --图片地址isLabel int, --1[name] varchar(200), --品牌名称logoImage varchar(500), --Logo图片discount varchar(200), --折扣startTime datetime, --开始时间endTime datetime, --结束时间type int -- -1=筹备中 0=已加入列表)goinsert into Activity(title,imageUrl,isLabel,[name],logoImage,discount,startTime,endTime,type)values('Anta','http://r.ytrss.com/rs/img/product/bargainproduct/bargain_APPTheme130504119744399979.jpg',0,'Anta','http://p10.ytrss.com/brand/brandlist/brand-615.jpg','2.4-4.4','2014-08-23','2014-09-23',0)goinsert into Activity(title,imageUrl,isLabel,[name],logoImage,discount,startTime,endTime,type)values('Ling-ning','http://r.ytrss.com/rs/img/product/bargainproduct/bargain_AppTheme130504119744399979.jpg',0,'Ling-ning','http://p10.ytrss.com/brand/brandlist/brand-615.jpg','
新闻热点
疑难解答