项目案例：网上书店数据库设计

CREATE TABLE `category` (
  `cateid` int(20) NOT NULL AUTO_INCREMENT,
  `catename` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cateid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `custmers` (
  `custid` int(20) NOT NULL,
  `custname` varchar(30) NOT NULL,
  `loginpwd` varchar(64) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address` varchar(80) DEFAULT NULL,
  `account` varchar(50) NOT NULL,
  PRIMARY KEY (`custid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `goods` (
  `goodsid` int(20) NOT NULL,
  `title` varchar(50) NOT NULL,
  `unitprice` decimal(20,0) NOT NULL,
  `discount` varchar(20) DEFAULT NULL,
  `cateid` int(20) DEFAULT NULL,
  `storeid` int(20) DEFAULT NULL,
  `roat` varchar(50) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,
  `packingexpense` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`goodsid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orderdetails` (
  `orderid` int(20) NOT NULL,
  `goodsid` int(20) NOT NULL,
  `qty` int(20) NOT NULL,
  `unitprice` decimal(20,0) NOT NULL,
  PRIMARY KEY (`orderid`,`goodsid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `store` (
  `storeid` int(20) NOT NULL AUTO_INCREMENT,
  `storename` varchar(50) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `personspending` int(20) DEFAULT NULL,
  PRIMARY KEY (`storeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
  `orderid` int(11) NOT NULL AUTO_INCREMENT,
  `doderno` varchar(255) NOT NULL,
  `orderprice` decimal(10,0) DEFAULT NULL,
  `orderdate` date DEFAULT NULL,
  `custname` varchar(20) DEFAULT NULL,
  `custphon` varchar(20) NOT NULL,
  `custaddress` varchar(40) NOT NULL,
  `zip` varchar(6) NOT NULL,
  `invoicetitle` varchar(50) DEFAULT NULL,
  `incoicecontent` varchar(20) DEFAULT NULL,
  `orderstatus` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;