PHP和MySQL Web開發從新手到高手,第3天-資料庫設計

標籤：

這些表在以後會用到.

1. 資料庫設計

  包含如下6個表:

 

2. 每個表的結構

2.1 表`author`

CREATE TABLE `author` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `password` char(32) DEFAULT NULL,  `email` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2827009 DEFAULT CHARSET=utf8

2.2 表`authorrole`

CREATE TABLE `authorrole` (  `authorid` int(11) NOT NULL,  `roleid` varchar(255) NOT NULL,  PRIMARY KEY (`authorid`,`roleid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.3 表`category`

CREATE TABLE `category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

2.4 表`joke`

CREATE TABLE `joke` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `joketext` text,  `jokedate` date NOT NULL,  `authorid` int(11) DEFAULT NULL,  `visible` enum(‘NO‘,‘YES‘) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8

2.5 表`jokecategory`

CREATE TABLE `jokecategory` (  `jokeid` int(11) NOT NULL,  `categoryid` int(11) NOT NULL,  PRIMARY KEY (`jokeid`,`categoryid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.6 表`role`

CREATE TABLE `role` (  `id` varchar(255) NOT NULL,  `description` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

PHP和MySQL Web開發從新手到高手,第3天-資料庫設計