--GWAP2.0-新豆網(wǎng)建表腳本

--刪除數(shù)據(jù)庫(kù)庫(kù)xindou
drop database if exists xindou;

--建里數(shù)據(jù)庫(kù)庫(kù)xindou，并指定解碼集為gb2312
--create database if not exists xindou default character set gb2312;
--建里數(shù)據(jù)庫(kù)庫(kù)xindou，并指定解碼集為utf8
create database if not exists xindou default character set utf8;

--使用數(shù)據(jù)庫(kù)
use xindou;

--分類表
drop table if exists category;
create table if not exists category(
	id int not null primary key auto_increment,
	cname varchar(20) not null,
	parentid int not null default 0,
	description varchar(200),
	photo blob,
	ctype tinyint default 1
) ENGINE=InnoDB;

--品牌表
drop table if exists brand;
create table if not exists brand(
	id int not null primary key auto_increment,
	en_name varchar(20),
	cn_name varchar(20),
	small_photo blob,
	big_photo blob,
	description varchar(2000)
) ENGINE=InnoDB;

--商品表
drop table if exists product;
create table if not exists product(
	id int not null primary key auto_increment,
	pname varchar(50) not null,
	cid int not null,
	recommend boolean default false,
	bid int not null,
	ptype varchar(30),
	price double not null,
	prisentation int default 0,
	promotion boolean default false,
	promotionalprice double,
	allowitem int,
	endtime datetime,
	addtime datetime not null,
	warranty varchar(20),
	remark varchar(2000),
	summary text,
	norm text,
	salepopluarity int not null default 0,
	viewpopluarity int not null default 0,
	totalscore double not null default 0.0,
	showscore double not null default 0.0,
	pricescore double not null default 0.0,
	performancescore double not null default 0.0,
	markuser text,
	collectuser text,
	INDEX(cid),
	FOREIGN KEY (cid) REFERENCES category(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX(bid),
	FOREIGN KEY (bid) REFERENCES brand(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

--商品關(guān)注表
drop table if exists productattention;
create table if not exists productattention(
	id int not null primary key auto_increment,
	sessionid varchar(50) not null,
	pid int not null,
	attentiontime datetime not null,
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--產(chǎn)品圖片表
drop table if exists photo;
create table if not exists photo(
	id int not null primary key auto_increment,
	pid int not null,
	image blob not null,
	description varchar(100),
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--產(chǎn)品顏色表
drop table if exists color;
create table if not exists color(
	id int not null primary key auto_increment,
	pid int not null,
	colorname varchar(20) not null,
	image blob not null,
	description varchar(100),
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--商城價(jià)格表
drop table if exists prices;
create table if not exists prices(
	id int not null primary key auto_increment,
	pid int not null,
	shopname varchar(30) not null,
	shopprice double not null,
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--分類規(guī)格表
drop table if exists norms;
create table if not exists norms(
	id int not null primary key auto_increment,
	cid int not null,
	normname varchar(20) not null,
	description varchar(200),
	INDEX (cid),
	FOREIGN KEY (cid) REFERENCES category(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--規(guī)格屬性表
drop table if exists normproperty;
create table if not exists normproperty(
	id int not null primary key auto_increment,
	nid int not null,
	properytname varchar(20) not null,
	description varchar(200) not null,
	INDEX (nid),
	FOREIGN KEY (nid) REFERENCES norms(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--商品規(guī)格表
drop table if exists productnorm;
create table if not exists productnorm(
	id int not null primary key auto_increment,
	pid int not null,
	nid int not null,
	npid int not null,
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE,
	INDEX (nid),
	FOREIGN KEY (nid) REFERENCES norms(id) ON UPDATE CASCADE ON DELETE CASCADE,
	INDEX (npid),
	FOREIGN KEY (npid) REFERENCES normproperty(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--支付方式表
drop table if exists payway;
create table if not exists payway(
	id int not null primary key auto_increment,
	isclose boolean not null default false,
	payname varchar(40) not null,
	paydesc varchar(200),
	fees double not null default 0
)ENGINE=InnoDB;

--支付參數(shù)表
drop table if exists payparameter;
create table if not exists payparameter(
	id int not null primary key auto_increment,
	pid int not null,
	pname varchar(30) not null,
	pkey varchar(50) not null,
	pvalue varchar(50) not null,
	description varchar(100),
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES payway(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--送貨方式表
drop table if exists sendway;
create table if not exists sendway(
	id int not null primary key auto_increment,
	isclose boolean not null default false,
	sendname varchar(40) not null,
	senddesc varchar(200),
	basefee double not null,
	arrivaldate varchar(20)
)ENGINE=InnoDB;

--收貨地址表
drop table if exists receiveaddress;
create table if not exists receiveaddress(
	id int not null primary key auto_increment,
	receivename varchar(20) not null,
	province varchar(30) not null,
	city varchar(30) not null,
	area varchar(30) not null,
	address varchar(80) not null,
	zip varchar(20) not null,
	phone varchar(21),
	mobile varchar(11)
)ENGINE=InnoDB;

--訂單表
drop table if exists orders;
create table if not exists orders(
	id varchar(15) not null primary key,
	ordertime datetime not null,
	state varchar(30) not null,
	description varchar(2000),
	productmoney double not null,
	pid int not null,
	sid int not null,
	fare double,
	fees double,
	ordermoney double,
	dounum int not null default 0,
	paymoney double not null,
	rid int not null,
	invoice boolean default false,
	receivedate varchar(20),
	invoicename varchar(40),
	invoicedesc varchar(1000),
	logisticscompany varchar(40),
	logisticsnumber varchar(50),
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES payway(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (sid),
	FOREIGN KEY (sid) REFERENCES sendway(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (rid),
	FOREIGN KEY (rid) REFERENCES receiveaddress(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--交易表
drop table if exists tarasaction;
create table if not exists transaction(
	id int not null primary key auto_increment,
	pid int not null,
	cid int,
	amount int not null,
	money double,
	oid varchar(15),
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (cid),
	FOREIGN KEY (cid) REFERENCES color(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (oid),
	FOREIGN KEY (oid) REFERENCES orders(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--問(wèn)題表
drop table if exists question;
create table if not exists question(
	id int not null primary key auto_increment,
	question varchar(40) not null
)ENGINE=InnoDB;

--級(jí)數(shù)表
drop table if exists levels;
create table if not exists levels(
	id int not null primary key auto_increment,
	levelname varchar(50) not null,
	photo varchar(100) not null,
	description varchar(200)
)ENGINE=InnoDB;

--會(huì)員表
drop table if exists user;
create table if not exists user(
	id int not null primary key auto_increment,
	username varchar(30) not null unique,
	password varchar(40) not null,
	sex boolean default false,
	nickname varchar(30),
	email varchar(50) not null,
	emailsecret boolean default false,
	mobile varchar(13) not null,
	phone varchar(21),
	homepage varchar(40),
	comefrom varchar(80),
	msn varchar(50),
	qq varchar(10),
	skype varchar(50),
	icq varchar(50),
	yahoo varchar(50),
	selfshow varchar(200),
	signature varchar(200),
	usesign boolean default false,
	useportrait boolean default false,
	portrait blob,
	portraitaddress varchar(100),
	portraitwidth int,
	portraitheight int,
	qid int,
	answer varchar(50),
	topicnum int,
	postnum int,
	tipwave bit(4),
	receiveemail boolean default true,
	hiden boolean default false,
	state boolean default false,
	logintime datetime,
	registertime datetime not null,
	lid int not null,
	medal varchar(50),
	dounum int default 0,
	forumpoint int default 0,
	paypoint int default 0,
	readaccess int default 0,
	INDEX (qid),
	FOREIGN KEY (qid) REFERENCES question(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (lid),
	FOREIGN KEY (lid) REFERENCES levels(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--操作記錄表
drop table if exists operatelog;
create table if not exists operatelog(
	id int not null primary key auto_increment,
	otime datetime not null,
	uname varchar(30) not null,
	operation varchar(200) not null,
	remark varchar(200),
	INDEX (uname),
	FOREIGN KEY (uname) REFERENCES user(username) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--收藏表
drop table if exists collect;
create table if not exists collect(
	id int not null primary key auto_increment,
	uid int not null,
	pid int not null,
	INDEX (uid),
	FOREIGN KEY (uid) REFERENCES user(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (pid),
	FOREIGN KEY (pid) REFERENCES product(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--公告表
drop table if exists announce;
create table if not exists announce(
	id int not null primary key auto_increment,
	username varchar(30) not null,
	atype varchar(10),
	title varchar(50) not null,
	titlecolor varchar(20),
	sendtime datetime not null,
	content text not null
)ENGINE=InnoDB;

--勛章表
drop table if exists medal;
create table if not exists medal(
	id int not null primary key auto_increment,
	medalname varchar(50) not null,
	photo blob not null,
	description varchar(200)
)ENGINE=InnoDB;

--版塊表
drop table if exists section;
create table if not exists section(
	id int not null primary key auto_increment,
	sectionname varchar(20) not null,
	parentid int not null default 0,
	description varchar(200),
	moderators varchar(200),
	photo blob,
	sectiontype bit
)ENGINE=InnoDB;

--主題表
drop table if exists topic;
create table if not exists topic(
	id int not null primary key auto_increment,
	islock boolean default false,
	lockuser varchar(30),
	topictype varchar(30),
	digest boolean default false,
	degestuser varchar(30),
	highlight boolean default false,
	lightcolor varchar(30),
	lightuser varchar(30),
	lettop boolean default false,
	lettopend datetime,
	lettoparea varchar(80),
	lettopuser varchar(30),
	sid int not null,
	vote boolean default false,
	voteuser text,
	viewnum int default 0,
	INDEX (sid),
	FOREIGN KEY (sid) REFERENCES section(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--主題關(guān)注表
drop table if exists topicattention;
create table if not exists topicattention(
	id int not null primary key auto_increment,
	sessionid  varchar(50) not null,
	tid int not null,
	attentiontime datetime not null,
	INDEX (tid),
	FOREIGN KEY (tid) REFERENCES topic(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--主題訂閱表
drop table if exists subscribe;
create table if not exists subscribe(
	id int not null primary key auto_increment,
	uid int not null,
	tid int not null,
	INDEX (uid),
	FOREIGN KEY (uid) REFERENCES user(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (tid),
	FOREIGN KEY (tid) REFERENCES topic(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--帖子表
drop table if exists post;
create table if not exists post(
	id int not null primary key auto_increment,
	title varchar(80) not null,
	transactionpoint int default 0,
	readaccess int default 0,
	icon int,
	content text not null,
	forbideurl boolean default false,
	forbidesmile boolean default false,
	forbidegerweb boolean default false,
	usesign boolean default false,
	floor int not null,
	sendtime datetime not null,
	tid int not null,
	uname varchar(30) not null,
	lastmodified datetime,
	INDEX (uname),
	FOREIGN KEY (uname) REFERENCES user(username) ON UPDATE CASCADE ON DELETE RESTRICT,
	INDEX (tid),
	FOREIGN KEY (tid) REFERENCES topic(id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

--消息表
drop table if exists message;
create table if not exists message(
	id int not null primary key auto_increment,
	sendname varchar(30) not null,
	receivename varchar(30) not null,
	title varchar(80) not null,
	content varchar(1000) not null,
	savebox boolean not null default false,
	sendtime datetime not null,
	isread boolean not null default false,
	messtype bit not null
)ENGINE=InnoDB;

--投票表
drop table if exists vote;
create table if not exists vote(
	id int not null primary key auto_increment,
	multiselect boolean not null default false,
	endtime datetime not null
)ENGINE=InnoDB;

--投票選項(xiàng)表
drop table if exists voteoption;
create table if not exists voteoption(
	id int not null primary key auto_increment,
	vid int not null,
	content varchar(100) not null,
	ballot int not null default 0,
	INDEX (vid),
	FOREIGN KEY (vid) REFERENCES vote(id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

--豆豆記錄表
drop table if exists beanlog;
create table if not exists beanlog(
	id int not null primary key auto_increment,
	uname varchar(30) not null,
	opertime datetime not null,
	operdesc varchar(50) not null,
	beannum int not null,
	INDEX (uname),
	FOREIGN KEY (uname) REFERENCES user(username) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;