7.数据库表格设计

• 1. 数据库表格ID

    数据库表格的ID，一般是每个表格的主键，ID生成规则的设计要根据具体情况定，以下是网上看到的一个不错的总结：

对于商城系统的ID生成问题，设计ID生成规则，需要考虑的问题有：
1）数据库自增，即从0开始，每次加1。在记录插入到数据库表时生成；这是mysql数据库的用户最多人的选择。不足之处是在新记录插入数据库前，并不知道它的值；第二个不足是无法在多个表记录间保持id唯一（某些系统会要求这点，这个理解起来有一定困难。）
2）GUID字符串：全局唯一标识符（GUID，Globally Unique Identifier）。GUID是一种由算法生成的二进制长度为128位的数字标识符。GUID主要用于在拥有多个节点、多台计算机的网络或系统中。在理想情况下，任何计算机和计算机集群都不会生成两个相同的GUID。GUID 的总数达到了2^128（3.4×10^38）个，所以随机生成两个相同GUID的可能性非常小，但并不为0。GUID一词有时也专指微软对UUID标准的实现。不足是字段类型必须是字符串，排序和性能都不如数字类型。
3）时间戳：精确到毫秒，意味着当同一个毫秒有多条记录生成时，id就可能重复，导致新记录无法插入。
4）自定义函数
超级表格某些关键表的id的生成，有下列要求：
①高性能，最好是数字类型，而不是字符串；
②提前生成（而不是在插入数据库后才生成），即在用户输入页面时就生成；
③不但要求本表唯一，而且不能与其它表记录的id重复。因为有些公共的表会把来自不同表的记录的id记录在一起。

    这里，我们的系统很简单，ID我们就采用数字类型，并且自增。

 

• 2.表格初步设计

   1）用户表格

CREATE TABLE `user` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

  

    2)商品表格

CREATE TABLE `commodity` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '商品名',
  `descr` varchar(1000) DEFAULT NULL COMMENT '商品描述',
  `picurl` varchar(255) DEFAULT NULL COMMENT '首页缩略图',
  `price` double(10,0) DEFAULT NULL COMMENT '价格',
  `stock` int(10) DEFAULT NULL COMMENT '库存',
  `category_id` int(255) DEFAULT NULL COMMENT '类别',
  `url1` varchar(255) DEFAULT NULL COMMENT '详细页图片1',
  `url2` varchar(255) DEFAULT NULL COMMENT '详细页图片2',
  `url3` varchar(255) DEFAULT NULL COMMENT '详细页图片3',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

 
    3）购物车商品表格

CREATE TABLE `cartcom` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `buy_num` int(10) DEFAULT NULL COMMENT '购买数量',
  `user_id` int(255) DEFAULT NULL COMMENT '购物车ID',
  `com_id` int(255) DEFAULT NULL COMMENT '商品ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

 
    4）商品类别表格

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tmenu` varchar(255) DEFAULT NULL COMMENT '类型',
  `smenu` varchar(255) DEFAULT NULL COMMENT '大类',
  `fmenu` varchar(255) DEFAULT NULL COMMENT '导航',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;

 

    初始数据：

INSERT INTO `category` VALUES ('4', 'Shirts', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('5', 'Sports Wear', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('6', 'Shorts', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('7', 'Suits & Blazers', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('8', 'Formal Shirts', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('9', 'Sweatpants', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('10', 'Swimwear', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('11', 'Trousers & Chinos', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('12', 'T-Shirts', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('13', 'Underwear & Socks', 'All Clothing', 'men');
INSERT INTO `category` VALUES ('14', 'Formal Shoes', 'Footwear', 'men');
INSERT INTO `category` VALUES ('15', 'Boots', 'Footwear', 'men');
INSERT INTO `category` VALUES ('16', 'Sports Shoes', 'Footwear', 'men');
INSERT INTO `category` VALUES ('17', 'Casual Shoes', 'Footwear', 'men');
INSERT INTO `category` VALUES ('18', 'Running Shoes', 'Footwear', 'men');
INSERT INTO `category` VALUES ('19', 'Sneakers', 'Footwear', 'men');
INSERT INTO `category` VALUES ('20', 'Loafers', 'Footwear', 'men');
INSERT INTO `category` VALUES ('21', 'Slippers', 'Footwear', 'men');
INSERT INTO `category` VALUES ('22', 'Sandals', 'Footwear', 'men');
INSERT INTO `category` VALUES ('23', 'Flip-flops', 'Footwear', 'men');
INSERT INTO `category` VALUES ('24', 'Levis', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('25', 'Persol', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('26', 'Nike', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('27', 'Edwin', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('28', 'New Balance', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('29', 'Jack & Jones', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('30', 'Paul Smith', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('31', 'Ray-Ban', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('32', 'Wood Wood', 'Popular Brands', 'men');
INSERT INTO `category` VALUES ('33', 'Shirts & Tops', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('34', 'Sports Wear', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('35', 'Kurtas & Kurties', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('36', 'Suits & Blazers', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('37', 'Sarees', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('38', 'Sweatpants', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('39', 'Swimwear', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('40', 'Night-Suits', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('41', 'T-Shirts', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('42', 'Jeans', 'All Clothing', 'women');
INSERT INTO `category` VALUES ('43', 'Heels', 'Footwear', 'women');
INSERT INTO `category` VALUES ('44', 'Flats', 'Footwear', 'women');
INSERT INTO `category` VALUES ('45', 'Sports Shoes', 'Footwear', 'women');
INSERT INTO `category` VALUES ('46', 'Casual Shoes', 'Footwear', 'women');
INSERT INTO `category` VALUES ('47', 'Running Shoes', 'Footwear', 'women');
INSERT INTO `category` VALUES ('48', 'Wedges', 'Footwear', 'women');
INSERT INTO `category` VALUES ('49', 'Boots', 'Footwear', 'women');
INSERT INTO `category` VALUES ('50', 'Pumps', 'Footwear', 'women');
INSERT INTO `category` VALUES ('51', 'Slippers', 'Footwear', 'women');
INSERT INTO `category` VALUES ('52', 'Flip-flops', 'Footwear', 'women');
INSERT INTO `category` VALUES ('53', 'Levis', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('54', 'Persol', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('55', 'Nike', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('56', 'Edwin', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('57', 'New Balance', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('58', 'Jack & Jones', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('59', 'Paul Smith', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('60', 'Ray-Ban', 'Popular Brands', 'women');
INSERT INTO `category` VALUES ('61', 'Wood Wood', 'Popular Brands', 'women');

 

    商品表格的数据也可以初始化一些：

INSERT INTO `commodity` VALUES ('1', 't-shirt1', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/1.jpg', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('2', 't-shirt2', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/2.jpg', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('3', 't-shirt3', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/1.jpg', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('4', 't-shirt4', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/2.jpg', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('5', 't-shirt5', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/1.jpg', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('6', 't-shirt6', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('7', 't-shirt7', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('8', 't-shirt8', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('9', 't-shirt9', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('10', 't-shirt10', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('11', 't-shirt11', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('12', 't-shirt12', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('13', 't-shirt13', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('14', 't-shirt14', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('15', 't-shirt15', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('16', 't-shirt16', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('17', 't-shirt17', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('18', 't-shirt18', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('19', 't-shirt19', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');
INSERT INTO `commodity` VALUES ('20', 't-shirt20', 'Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old.', '/images/pi.png', '100', '20000', '4', '/images/si.jpg', '/images/si1.jpg', '/images/si2.jpg');