`
sophia828
  • 浏览: 9793 次
社区版块
存档分类
最新评论

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');

 

    

 

 

分享到:
评论

相关推荐

    1.数据库1

    7. 数据库安全性:数据库安全涉及用户权限管理、访问控制、审计跟踪、加密和备份恢复等措施,以保护数据免受未经授权的访问和篡改。 8. 数据库备份与恢复:定期备份数据库是防止数据丢失的重要策略。备份可以是完整...

    Delphi.7.高效数据库程序设计.rar

    《Delphi 7 高效数据库程序设计》是面向Delphi开发者的一本深入教程,它主要探讨如何在Delphi 7环境下进行高效、稳定的数据库应用开发。Delphi 7是Borland公司(现Embarcadero Technologies)推出的一款集成开发环境...

    数据库原理课程设计数据库原理课程设计.doc

    7. 数据库事务:数据库事务是一组数据库操作,这些操作要么全部执行,要么全部不执行,以保持数据的一致性。 8. 数据库安全性:包括用户权限管理、访问控制、审计和加密等机制,以防止未授权访问和数据泄露。 9. ...

    车辆管理系统数据库设计课程设计

    7. 数据库 normalization:数据库 normalization 是指将数据库设计成符合一定规则的形式,以提高数据的安全性和一致性。在车辆管理系统中,数据库 normalization 是指将车辆管理系统数据库设计成符合一定规则的形式...

    HNU数据库原理数据库设计与应用开发大作业

    在“HNU数据库原理数据库设计与应用开发大作业”中,我们可以探讨一系列关于数据库理论、设计与实际应用的重要知识点。数据库是信息系统的核心组成部分,用于存储、管理并检索数据,尤其在信息化社会中扮演着至关...

    数据库课程设计.zip

    数据库课程设计是一个重要的学习环节,它旨在让学生深入理解数据库系统的工作原理,并掌握如何设计、实现和管理实际数据库项目。在这一过程中,学生通常会接触到多种关键知识点,包括但不限于以下内容: 1. 数据库...

    图书销售系统的数据库设计.pdf

    7. 图书销售系统的数据库设计需要考虑用户的需求、数据的存储和管理、系统的安全性等多方面的因素。 8. 数据库设计需要使用结构化分析方法(SA方法),对需求进行分解、抽象和设计。 9. 数据库设计需要使用E-R图描述...

    数据库基础设计与实现(上)

    7. 数据库备份与恢复:为了防止数据丢失,数据库需要定期备份,并在必要时进行恢复。备份策略有完整备份、增量备份和差异备份等,恢复则依赖于事务日志和备份策略。 8. 数据库并发控制:在多用户环境中,数据库管理...

    上课讲的的教案 数据库教案 数据库教案 数据库教案................

    7. 数据库教学应用: - 教学实例:通过实际案例让学生理解数据库原理,如学生信息管理系统、图书馆管理系统等。 - 实验操作:让学生亲手操作数据库,学习SQL语句,理解数据库操作流程。 - 项目开发:引导学生参与...

    数据库课程设计某医院病房计算机系统-毕业论文.doc

    1. 数据库设计:课程设计报告中详细介绍了数据库设计的过程,包括E-R图的设计、关系模式的设计、建表格的组成等。这些知识点对于数据库设计至关重要。 2. 数据库系统的应用:在报告中,数据库课程设计的是某医院...

    校园外卖系统数据库设计.docx

    【校园外卖系统数据库设计】 校园外卖系统数据库设计旨在满足高校内学生对外卖服务的需求,通过高效的数据管理和交互,实现从下单到配送的便捷流程。该系统主要包括四个核心部分:餐厅信息、菜品信息、顾客信息和...

    数据库课后习题答案

    关系数据库模型是最常见的一种,它基于二维表格结构,每个表由多个字段(列)和记录(行)组成。在解答课后习题时,会涉及到如何设计合理的关系模型,避免数据冗余和不一致性。 其次,数据库设计包括概念设计、逻辑...

    shujukuyuanli.rar_数据库 教程_数据库原理

    7. 数据库事务与并发控制:事务是数据库操作的基本单位,保证了数据的一致性。并发控制则处理多个用户同时访问数据库时可能出现的问题,如死锁和丢失更新。 8. 数据备份与恢复:数据库的备份是为了防止数据丢失,...

    学校机房管理系统数据库课程设计报告.doc

    学校机房管理系统数据库课程设计报告 概括来说,该报告是一个数据库课程设计报告,旨在设计一个学校机房管理系统的数据库。下面是从报告中提取的相关知识点: 1. 需求分析 在设计数据库之前,需要对系统的需求...

    数据库课程设计_个人通讯录

    【数据库课程设计_个人通讯录】是一个以C++编程语言实现的项目,旨在帮助学习者理解和应用数据库原理。在这个设计中,个人通讯录系统作为实际案例,展示了如何将理论知识转化为实际操作。该项目使用了Visual Studio ...

    数据库系统概论表格笔记

    这篇“数据库系统概论表格笔记”提供了对这一领域的深入理解和学习的框架。笔记可能涵盖了数据库的基本概念、模型、设计方法以及管理策略,旨在帮助读者建立起系统的数据库知识体系。 一、基本概念 1. 数据与信息:...

    数据库课程设计要求.docx

    数据库课程设计是计算机科学教育...3. 关键表格设计:如用户表、会员表、销售表、交易表和进货入库表等。 通过这样的课程设计,学生能够全面地理解数据库系统的设计、实现和管理,为未来从事相关工作打下坚实的基础。

    计算机技术基础题库(信息技术、数据库、SQL、多媒体、计算机组成、操作系统、多媒体技术、程序设计基础).doc

    3. 在数据库中,数据以表格形式存在,每个表格称为表或关系,包含一系列行和列。列定义了数据的类型,行则代表单个记录。 4. 数据库管理系统(DBMS)是用于创建、维护和管理数据库的软件,确保数据的安全性、完整性...

    数据库系统概论电子教案

    7. 数据库安全性: 数据库安全涉及权限管理、角色分配、审计追踪等,确保只有授权用户才能访问特定数据,并记录所有数据库活动。 8. 数据库事务: 事务是一系列数据库操作的逻辑单元,必须全部成功或全部失败。...

    数据库课程设计 学院认识管理

    数据库课程设计是IT教育中的重要组成部分,特别是在学院的人事管理领域。这个设计项目旨在让学生深入理解和应用数据库管理系统(DBMS)的基本概念、设计原则以及实际操作技能。在本项目中,我们将探讨以下几个关键...

Global site tag (gtag.js) - Google Analytics