首页 > 代码库 > ylbtech-dbs-m-ele(饿了么)

ylbtech-dbs-m-ele(饿了么)

ylbtech-dbs:ylbtech-m-ele(饿了么)

-- =============================================
-- DatabaseName:Ele

-- desc:饿了么(外卖网)
-- pubdate:10:41 2014-06-23
-- author:ylbtech
-- http://m.ele.me
-- =============================================

1.A,数据库关系图(Database Diagram) 返回顶部

 

1.B,数据库设计脚本(Database Design Script)返回顶部

1.B.1,

1.B.1.1, sql-basic.sql

技术分享
-- =============================================-- DatabaseName:Ele-- pubdate:10:41 2014-06-23-- author:ylbtech-- http://m.ele.me-- =============================================USE masterGO-- Drop the database if it already existsIF  EXISTS (    SELECT name         FROM sysdatabases         WHERE name = Nele)DROP DATABASE eleGOCREATE DATABASE eleGOuse elegogo-- =============================================-- ylb:1,用户表-- =============================================create table Account(account_id int primary key identity(10000,1),    --编号【PK】username varchar(40) unique not null,            --用户名【UQ】[password] varchar(40) not null,                --密码email varchar(60) not null,        --电子邮箱pubdate datetime default(getdate()),    --时间flag bit default(0)    --标识帐号是否激活 0:未激活;1:以激活)go-- =============================================-- ylb:1,我的地址-- =============================================create table [Address](address_id int primary key identity(10000,1),    --编号【PK】[address] varchar(40) not null,            --详细地址phone varchar(40) not null,                --联系电话phone_bk varchar(40) not null,        --备选电话flag bit default(0),    --0:;1:默认送餐地址account_id int foreign key references Account(account_id),   --账户ID【FK】 )go--drop table FeedBackGO-- =============================================-- ylb: 6, 反馈留言-- =============================================create table Feedback(feedback_id int primary key identity(1,1),    --编号【PK,ID】content varchar(200),                    --内容pubdate datetime default(getdate()),    --时间[type] int,                            --类型[status] int,                            --状态reply_content varchar(200),                --回复内容reply_pubdate datetime,            --回复日期account_type int,        --用户类型account_id int foreign key references Account(account_id)   --账户ID【FK】 )go -- ============================================= -- 2,标签 【公共】-- ============================================= create table Tag ( tag_id int identity(1,1) primary key,   --类别ID  [PK] tag_name varchar(100), --标签名称 tag_img varchar(100),    --标签图片[description] varchar(400),    --描述flag bit default(0)    --是否禁用) --drop table Place--drop table Citygo-- =============================================-- ylb:1,城市【公共】-- =============================================create table City(city_id varchar(20) primary key,    --编号【PK】city_name varchar(40) unique not null,            --城市名【UQ】flag bit default(0)    --0:;1:是否禁用)go-- =============================================-- ylb:1,城市【公共】-- =============================================create table Place(place_id int unique identity(10000,1),    --编号【UQ】place_name varchar(40) unique not null,            --地址名称address varchar(40) not null,            --地址flag bit default(0),    --0:;1:是否禁用city_id varchar(20) foreign key references City(city_id),   --账户ID【FK】 )gogo-- =============================================-- ylb:1,用户表_商户-- =============================================create table AccountShop(account_shop_id int primary key identity(10000,1),    --编号【PK】username varchar(40) unique not null,            --用户名【UQ】[password] varchar(40) not null,                --密码email varchar(60) not null,        --电子邮箱pubdate datetime default(getdate()),    --时间flag bit default(0)    --标识帐号是否激活 0:未激活;1:以激活)go-- =============================================-- ylb:1,店铺-- =============================================create table Shop(shop_id int primary key identity(10000,1),    --编号【UQ】shop_name varchar(500) unique not null,            --商铺名称logo_img varchar(500) not null,            --商标图片opening_time varchar(500) not null,            --营业时间begin_price varchar(500) not null,            --起送价[address] varchar(500) not null,            --地址intro varchar(500) not null,            --简介notice varchar(500) not null,            --公告location varchar(40) not null,        --商铺所在位置[status] varchar(40),    --状态 营业中|休息中--distance varchar(40) not null,            --距离account_shop_id int foreign key references AccountShop(account_shop_id)   --商户账户ID【FK】 )go -- ============================================= -- 2,类别 -- ============================================= create table Category ( category_id int identity(10000,1) primary key,   --类别ID  [PK] category_name varchar(40) not null, --类别名称 [description] varchar(400),                --说明 picture varchar(40),                       --图片flag bit default(0),    --是否禁用shop_id int foreign key references Shop(shop_id)   --商铺ID【FK】 ) go--drop table Product  go -- ============================================= --3,产品  -- ============================================= create table Product( product_id int identity primary key, --产品ID『PK』 product_name varchar(400) not null,  --产品名称 product_img varchar(400),    --图片quantity_per_unit varchar(40),   --规格   unit_price decimal(8,2),            --单价 units_in_stock int default(0) check(units_in_stock>=0),     --库存量 units_on_order int default(0) check(units_on_order>=0),     --订购量--reorder_level int default(0) check(reorder_level>=0),     --再订购量 flag bit default(0),    --是否禁用category_id int foreign key references Category(category_id),                   --类别ID shop_id int foreign key references Shop(shop_id),    --帐户编号【FK】关联与帐户设置flag_hotfood bit default(0)    --是否推荐) --drop table Commentgo -- ============================================= -- 4,菜品评价 -- ============================================= create table Comment(comment_id int identity primary key,    --编号【PK,ID】content varchar(400),    --内容pubdate datetime default(getdate()),    --评价日期account_id int foreign key references Account(account_id),   --账户ID【FK】 shop_id int foreign key references Shop(shop_id),   --帐户编号【FK】关联与帐户设置product_id int foreign key references Product(product_id),   --菜品ID【FK】 )go -- ============================================= -- 7,订单 -- ============================================= create table [Order]( order_id int identity primary key,   --订单ID【PK】 account_id int foreign key references Account(account_id),   --账户ID【FK】 shop_id int foreign key references Shop(shop_id),    --商铺ID【FK】 order_date datetime,     --订购日期 required_date datetime,  --到货日期 total decimal(8,2),        --合计金额shipped_date datetime,   --发货日期 ShipVia int,        --运货商【FK】 fright decimal(8,2),           --运货费 ship_name varchar(15),      --货主名称 ship_address varchar(60),   --货主地址   ship_city varchar(15),      --货主城市 ship_region varchar(15),    --货主地区      ship_contry varchar(15),     --货主国家 ship_postal_code varchar(10),--货主邮政编码[status] int,    --状态flag bit default(0),    --是否禁用deliver_time varchar(40),    --送餐时间remark varchar(400),        --备注[type] int    --外卖|预订|就餐) go--drop table [Order]--drop table OrderDetailsgo -- ============================================= -- 4,订单明细 -- ============================================= create table OrderDetails( order_id int,      --订单ID【UPK】 product_id int,      --产品ID【UPK】    unit_price decimal(8,2) not null,   --单价 quantity int not null, --数量 --discount decimal(8,2) not null,     --折扣 [name] varchar(400),    --名称status int,    --状态account_id int foreign key references Account(account_id),   --账户ID【FK】 shop_id int foreign key references Shop(shop_id),    --商铺ID【FK】 primary key(order_id,product_id)  --联合主键 ) --drop table Invoicego-- =============================================-- ylb:1,发票-- =============================================create table Invoice(invoice_id int primary key identity(100,1),    --编号【PK】invoice varchar(40) not null,            --发票抬头[money] decimal(8,2),                --金额pubdate datetime default(getdate()),    --开票时间[status] varchar(40),        --状态 正常|作废account_id int foreign key references Account(account_id),   --账户ID【FK】 shop_id int foreign key references Shop(shop_id),   --商铺ID【FK】order_id int foreign key references [Order](order_id)   --订单ID【FK】 )go-- =============================================-- ylb:1,购物车-- =============================================create table Cart([id] int,    --菜品编号【FK】[name] varchar(400),    --名称quantity int,        --数量unit_price decimal(8,2),    --单价[type] int,    --状态 0=登录|1=匿名shop_id int,    --商铺ID【FK】account_id int,     --账户ID【FK】auth_cookie varchar(400),    --匿名Cookie 记录pubdate datetime    --添加时间)
View Code

1.B.1.2, insertTestData.sql

技术分享
use elegoselect * from AccountShopinsert into AccountShop(username,[password],email,pubdate,flag)values(rainShop,lifeel_,1102307900@qq.com,2014-06-12,1)goselect * from Shopinsert into Shop(shop_name,logo_img,opening_time,begin_price,address,intro,notice,location,status,account_shop_id)values(RainShop,img,10:00-17:00,20元,长远天地,Intro  Intro。。。,Notice Notice。。。。,Location,营业中,10000)goselect * from Categoryinsert into Category(category_name,[description],picture,flag,shop_id)values(套餐,套餐,picture,1,10000)insert into Category(category_name,[description],picture,flag,shop_id)values(单点,单点,picture,1,10000)goselect * from Productinsert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(酸辣土豆丝饭,img,‘‘,12,10,10,1,10000,10000,0)insert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(鱼香肉丝饭,img,‘‘,12,10,10,1,10000,10000,0)insert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(卤肉饭,img,‘‘,12,10,10,1,10000,10000,0)insert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(鸡肉饭,img,‘‘,12,10,10,1,10000,10000,0)goinsert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(牛肉饭,img,‘‘,12,10,10,1,10001,10000,0)insert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(羊肉饭,img,‘‘,12,10,10,1,10001,10000,0)insert into Product(product_name,product_img,quantity_per_unit,unit_price,units_in_stock,units_on_order,flag,category_id,shop_id,flag_hotfood)values(鸭肉饭,img,‘‘,12,10,10,1,10001,10000,0)goselect * from Taginsert into Tag(tag_name,tag_img,[description],flag) values(,restaurant-icons invoice tooltip-on,该餐厅支持开发票,开票订单金额最低30元起,请在下单时填写好发票抬头,0)insert into Tag(tag_name,tag_img,[description],flag) values(,restaurant-icons deliver-fee tooltip-on,该餐厅订餐需支付配送费5元,0)insert into Tag(tag_name,tag_img,[description],flag) values(,restaurant-icons extra-discount tooltip-on,该餐厅支持立减优惠。每单满100元立减2元,0)goselect * from City--insert into City(city_id,city_name,flag) values(‘010‘,‘北京‘,0)--insert into City(city_id,city_name,flag) values(‘021‘,‘上海‘,0)--insert into City(city_id,city_name,flag) values(‘022‘,‘天津‘,0)insert into City(city_id,city_name,flag) values(3,北京,0)insert into City(city_id,city_name,flag) values(1,上海,0)insert into City(city_id,city_name,flag) values(5,天津,0)
View Code

1.B.2,

1.C,功能实现代码(Function Implementation Code)返回顶部

 

技术分享作者:ylbtech
出处:http://ylbtech.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

ylbtech-dbs-m-ele(饿了么)