数据库设计.txt

学生社团管理系统 学生社团管理系统

数据库设计：

用户信息：user
	字段：
	1	ID:auto
	2	LOCATIONID		//从属的部门,如社联
	3	USERNAME 		//用户名
	4	PASSWORD		//密码
	5	DEPARTMENTID		//部门ID
	6	DEPARTMENTNAME		//部门名称
	7	IFOFFSET		//分支部门
	8	INTRODUCTION		//简介
	9	LOGO			//图标

	
新闻：(news)

	字段：	
	1	ID: auto  	//序列号
	2	locationID: 	//location
	3	departmentID:	//department
	4	departmentName	//departmentName
	4	Date		//date
	5	reworkDate	//
	6	ifTop:		//置顶
	7	title		//标题
	8	content		//内容


留言版   (note)

	字段：	1 ID:auto		//
		2 from		//来自
		3 toid		//留言对象的ID号
		4 time		//时间
		5 reverttime	//回复时间
		6 hide		//是否是悄悄话
		7 content		//内容
		8 revert		//回复

短消息	(message)
	字段：	ID:auto		
		Fromid		//发送者ID
		DEPTNAMEFROM	//FROM DEPARTMENT NAME
		toid		//接收者ID
		DEPTNAMETO	//TO DEPARTMENT NAME
		time		//时间
		title		//title
		content		//内容





成员信息 (personalInfo)

审核表存档 (audit)



SQL

insert into user (username,password,departmentname,introduction) values('root','123','root','root');

message

DROP TABLE IF EXISTS `mydb`.`message`;
CREATE TABLE  `mydb`.`message` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `FROMID` int(10) unsigned NOT NULL default '0',
  `DEPTNAMEFROM` varchar(45) character set gbk NOT NULL default '',
  `TOID` int(10) unsigned NOT NULL default '0',
  `DEPTNAMETO` varchar(45) character set gbk NOT NULL default '',
  `TIME` datetime NOT NULL default '0000-00-00 00:00:00',
  `TITLE` varchar(45) character set gbk NOT NULL default '',
  `CONTENT` mediumtext character set gbk NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

news

DROP TABLE IF EXISTS `mydb`.`news`;
CREATE TABLE  `mydb`.`news` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `LOCATIONID` int(10) unsigned NOT NULL default '0',
  `DEPARTMENTID` int(10) unsigned NOT NULL default '0',
  `DEPARTMENTNAME` varchar(45) character set gbk NOT NULL default '',
  `DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `REWORKDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `IFTOP` tinyint(1) NOT NULL default '0',
  `TITLE` varchar(45) character set gbk NOT NULL default '',
  `CONTENT` mediumtext character set gbk NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

note:

DROP TABLE IF EXISTS `mydb`.`note`;
CREATE TABLE  `mydb`.`note` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `FROMNAME` varchar(30) character set gbk NOT NULL default '',
  `TOID` int(10) unsigned NOT NULL default '0',
  `TIME` datetime NOT NULL default '0000-00-00 00:00:00',
  `REVERTTIME` datetime NOT NULL default '0000-00-00 00:00:00',
  `HIDE` tinyint(1) NOT NULL default '0',
  `CONTENT` mediumtext character set gbk NOT NULL,
  `REVERT` mediumtext character set gbk NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

user

DROP TABLE IF EXISTS `mydb`.`user`;
CREATE TABLE  `mydb`.`user` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `LOCATIONID` int(10) unsigned NOT NULL default '0',
  `USERNAME` varchar(45) character set gbk NOT NULL default '',
  `PASSWORD` varchar(45) character set gbk NOT NULL default '',
  `DEPARTMENTID` int(10) unsigned NOT NULL default '0',
  `DEPARTMENTNAME` varchar(45) character set gb2312 collate gb2312_bin NOT NULL default '',
  `IFOFFSET` tinyint(1) NOT NULL default '0',
  `INTRODUCTION` mediumtext character set gbk NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;