精华内容
下载资源
问答
  • 数据库表设计

    千次阅读 2020-10-16 15:03:06
    我们在设计数据库的时候,是否会突破常规,找到最适合自己需求的设计方案,下面来举个例子: 常用的邻接表设计,都会添加 一个 parent_id 字段,比如区域(国、省、市、区): CREATE TABLE Area( [id]...

    我们在设计数据库的时候,是否会突破常规,找到最适合自己需求的设计方案,下面来举个例子:

     

    常用的邻接表设计,都会添加 一个 parent_id 字段,比如区域表(国、省、市、区):

     

    CREATE TABLE Area (

    [id] [int]  NOT NULL,

    [name] [nvarchar]  (50) NULL,

    [parent_id] [int]  NULL,

    [type] [int]  NULL );

     

    name:地域的名称, parent_id 是父ID,省的父ID是国,市的父ID 为省,以此类推。

     

    type 是区域的阶级: 1:国,2:省,3:市,4:区

     

    在层级比较确定的情况下,这么设计表格没有什么问题,调用起来也很方便。

     

    但是使用这种邻接表设计方式,并不能满足所有的需求,当我们不确定层级的情况下,假设我有下面一个评论结构:

    用邻接表记录这个评论的数据(comments 表):

    大家有没发现,这么设计表,如果要查询一个节点的所有后代,是很难实现的,你可以使用关联查询来获取一条评论和他的后代:

     

    SELECT c1.*, c2.* FROM comments c1 LEFT OUTER JOIN comments c2 ON c2.parent_id = c1.comment_id;

    然而这个查询只能获取两层的数据。这种树的特性就是可以任意深地拓展,你需要有相应的方法来获取它的深度数据。比如,可能需要计算一个评论分支的数量,或者计算一个机械设备的所有的总开销。

     

    某些情况下,在项目中使用邻接表正好适用。邻接表设计的优势在于能快速的获取一个给定节点的直接父子节点,它也很容易插入新节点。如果这样的需求就是你的项目对于分层数据的全部操作,那使用邻接表就可以很好的工作了。

     

    遇到上述的树模型,有几种方案是可以考虑下的:路径枚举、嵌套集以及闭包表。这些解决方案通常看上去比邻接表复杂很多,但它们的确使得某些使用邻接表比较复杂或很低效的操作变得更简单。如果你的项目确实需要提供这些操作,那么这些设计会是邻接表更好的选择。

     

    一、路径枚举

     

    在comments 表中,我们使用类型varchar 的path 字段来替代原来的parent_id 字段。这个path 字段所存储的内容为当前节点的最顶层祖先到它的自己的序列,就像UNIX的路径一样,你甚至可以使用 ‘/’ 作为路径的分隔符。

    你可以通过比较每个节点的路径来查询一个节点祖先。比如:要找到评论#7, 路径是 1/4/5/7一 的祖先,可以这么做:

     

    SELECT * FROM comments AS c WHERE '1/4/5/7' LIKE c.path || '%' ;

     

    这句话查询语句会匹配到路径为 1/4/5/%,1/4/% 以及 1/% 的节点,而这些节点就是评论#7的祖先。

     

    同时还可以通过将LIKE 关键字两边的参数互换,来查询一个给定节点的所有后代。比如查询评论#4,路径path为 ‘1/4’ 的所有后代,可以使用如下语句:

     

    SELECT * FROM comemnts AS c WHERE c.path LIKE '1/4' || '%' ;

     

    这句查询语句所有能找到的后台路径分别是:1/4/5、1/4/5/6、1/4/5/7。

     

    一旦你可以很简单地获取一棵子树或者从子孙节点到祖先节点的路径,你就可以很简单地实现更多的查询,如查询一颗子树所有节点上值的总和。

     

    插入一个节点也可以像使用邻接表一样地简单。你所需要做的只是复制一份要插入节点的父亲节点路径,并将这个新节点的ID追加到路径末尾即可。

     

    路径枚举也存在一些缺点,比如数据库不能确保路径的格式总是正确或者路径中的节点确实存在。依赖于应用程序的逻辑代码来维护路径的字符串,并且验证字符串的正确性开销很大。无论将varchar 的长度设定为多大,依旧存在长度的限制,因而并不能够支持树结构无限扩展。

     

    二、 嵌套集

     

    嵌套集解决方案是存储子孙节点的相关信息,而不是节点的直接祖先。我们使用两个数字来编码每个节点,从而表示这一信息,可以将这两个数字称为nsleft 和 nsright。

     

    每个节点通过如下的方式确定nsleft 和nsright 的值:nsleft的数值小于该节点所有后代ID,同时nsright 的值大于该节点的所有后代的ID。这些数字和comment_id 的值并没有任何关联。

     

    确定这三个值(nsleft,comment_id,nsright)的简单方法是对树进行一次深度优先遍历,在逐层深入的过程中依次递增地分配nsleft的值,并在返回时依次递增地分配nsright的值。得到数据如下:

     

    一旦你为每个节点分配了这些数字,就可以使用它们来找到指定节点的祖先和后代。比如搜索评论#4及其所有后代,可以通过搜索哪些节点的ID在评论 #4 的nsleft 和 nsright 范围之间,例:

     

    SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c2.nsleft BETWEEN c1.nsleft

    AND c1.nsright WHERE c1.comment_id = 4;

     

    比如搜索评论#6及其所有祖先,可以通过搜索#6的ID在哪些节点的nsleft 和 nsright 范围之间,例:

     

    SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c1.nsleft BETWEEN c2.nsleft

    AND c2.nsright WHERE c1.comment_id = 6;

     

    使用嵌套集设计的主要优势是,当你想要删除一个非叶子节点时,它的后代会自动替代被删除的节点,成为其直接祖先节点的直接后代。就是说已经自动减少了一层。

     

    然而,某些在邻接表的设计中表现得很简单的查询,比如获取一个节点的直接父亲或者直接后代,在嵌套集设计中会变得比较复杂。在嵌套集中,如果需要查询一个节点的直接父亲,我们会这么做,比如要找到评论#6 的直接父亲:

     

    SELECT parent.* FROM comments AS c JOIN comments AS parent ON c.nsleft BETWEEN parent.nsleft AND parent.nsright

    LEFT OUTER JOIN comments AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright

    AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6

    AND in_between.comment_id IS NULL;

     

    总之有些复杂。

     

    对树进行操作,比如插入和移动节点,使用嵌套集会比其它设计复杂很多。当插入一个新节点时,你需要重新计算新插入节点的相邻兄弟节点、祖先节点和它祖先节点的兄弟,来确保他们的左右值都比这个新节点的左值大。同时,如果这个新节点时一个非叶子节点,你还要检查它的子孙节点。

     

    如果简单快速查询是整个程序中最重要的部分,嵌套集是最好的选择,比操作单独的节点要方便快捷很多。然而,嵌套集的插入和移动节点是比较复杂的,因为需要重新分配左右值,如果你的应用程序需要频繁的插入、删除节点,那么嵌套集可能并不合适。

     

    三、闭包表

     

    闭包表是解决分级存储的一个简单而优雅的解决方案,它记录了树中所有节点间的关系,而不仅仅只有那些直接的父子节点。

     

    在设计评论系统时,我们额外创建了一个叫 tree_paths 表,它包含两列,每一列都指向 comments 中的外键。

     

    我们不再使用comments 表存储树的结构,而是将树中任何具有(祖先 一 后代)关系的节点对都存储在treepaths 表里,即使这两个节点之间不是直接的父子关系;同时,我们还增加一行指向节点自己。

    通过treepaths 表来获取祖先和后代比使用嵌套集更加的直接。例如要获取评论#4的后代,只需要在 treepaths 表中搜索祖先是评论 #4的行就行了。同样获取后代也是如此。

     

    要插入一个新的叶子节点,比如评论#6的一个子节点,应首先插入一条自己到自己的关系,然后搜索 treepaths 表中后代是评论#6 的节点,增加该节点和新插入节点的“祖先一后代”关系(新节点ID 应该为8):

     

    INSERT INTO treepaths (ancestor, descendant)

    SELECT t.ancestor, 8

    FROM treepaths AS t

    WHERE t.descendant = 6

    UNION ALL SELECT 8, 8;

     

    要删除一个叶子节点,比如评论#7, 应删除所有treepaths 表中后代为评论 #7 的行:

     

    DELETE FROM treepaths WHERE descendant = 7;

     

    要删除一颗完整的子树,比如评论#4 和它所有的后代,可删除所有在 treepaths 表中后代为 #4的行,以及那些以评论#4后代为后代的行。

     

    闭包表的设计比嵌套集更加的直接,两者都能快捷地查询给定节点的祖先和后代,但是闭包表能更加简单地维护分层信息。这两个设计都比使用邻接表或者路径枚举更方便地查询给定节点的直接后代和祖先。

     

    然而你可以优化闭包表来使它更方便地查询直接父亲节点或者子节点: 在 treepaths 表中添加一个 path_length 字段。一个节点的自我引用的path_length 为0,到它直接子节点的path_length 为1,再下一层为2,以此类推。这样查询起来就方便多了。

     

    总结:你该使用哪种设计?

     

    每种设计都各有优劣,如何选择设计,依赖于应用程序的哪种操作是你最需要性能上的优化。

    层级数据设计比较

     

    1、邻接表是最方便的设计,并且很多程序员都了解它

     

    2、如果你使用的数据库支持WITH 或者 CONNECT BY PRIOR 的递归查询,那能使得邻接表的查询更高效。

     

    3、枚举路径能够很直观地展示出祖先到后代之间的路径,但同时由于它不能确保引用完整性,使得这个设计非常脆弱。枚举路径也使得数据的存储变得比较冗余。

     

    4、嵌套集是一个聪明的解决方案,但可能过于聪明,它不能确保引用完整性。最好在一个查询性能要求很高而对其他要求一般的场合来使用它。

     

    5、闭包表是最通用的设计,并且以上的方案也只有它能允许一个节点属于多棵树。它要求一张额外的表来存储关系,使用空间换时间的方案减少操作过程中由冗余的计算所造成的消耗。

     

    这几种设计方案只是我们日常设计中的一部分,开发中肯定会遇到更多的选择方案。选择哪一种方案,是需要切合实际,根据自己项目的需求,结合方案的优劣,选择最适合的一种。

     

    我遇到一些开发人员,为了敷衍了事,在设计数据库表时,只考虑能否完成眼下的任务,不太注重以后拓展的问题,不考虑查询起来是否耗性能。可能前期数据量不多的时候,看不出什么影响,但数据量稍微多一点的话,就已经显而易见了(例如:可以使用外联接查询的,偏偏要使用子查询)。

     

    我觉得设计数据库是一个很有趣且充满挑战的工作,它有时能体现你的视野有多宽广,有时它能让你睡不着觉,总之痛并快乐着。

    展开全文
  • 基于RBAC权限管理数据库表设计

    热门讨论 2013-01-15 17:25:36
    基于RBAC权限管理数据库表设计
  • 用户登录系统数据库表设计 最近看了看公司后台用户登录系统的设计, 比较混乱, 主要还是因为URS和Oauth以及URS第三方这三个登录形式各不相同导致的。 下面着重介绍一下涉及到第三方登录中需要注意的...

    最近看了看公司后台用户登录系统的设计, 比较混乱, 主要还是因为URS和Oauth以及URS第三方这三个登录形式各不相同导致的。

    下面着重介绍一下涉及到第三方登录中需要注意的问题

     

     

    在一个新项目中, 如果是要建立自己的登录体系的话, 那么直接创建一个Users表,包含usernamepassword两列,这样,就可以实现登录了:

     id | username | password | name等其他字段
    ----+----------+----------+----------------
     A1 | bob      | a1b23f2c | ...
     A2 | adam     | c0932f32 | ...
    

    如果要让用户通过第三方登录,比如微博登录或QQ登录,怎么集成进来呢?

    以微博登录为例,由于微博使用OAuth2协议登录,所以,一个登录用户会包含他的微博身份的ID,一个Access Token用于代表该用户访问微博的API和一个过期时间。

    要集成微博登录,很多童鞋立刻想到把Users表扩展几列,记录下微博的信息:

     id | username | password | weibo_id | weibo_access_token | weibo_expires | name等其他字段
    ----+----------+----------+----------+--------------------+---------------+----------------
     A1 | bob      | a1b23f2c | W-012345 | xxxxxxxxxx         | 604800        | ...
     A2 | adam     | c0932f32 | W-234567 | xxxxxxxxxx         | 604800        | ...
    

    加一个QQ登录Users表就又需要加3列,非常不灵活

     

    那么我们需要对这个表进行拆分。当用户以任意一种方式登录成功后,我们读取到的总是Users表对应的一行记录,它实际上是用户的个人资料(Profile),而登录过程只是为了认证用户(Authenticate),无论是本地用密码验证,还是委托第三方登录,这个过程本质上都是认证。

    所以,如果把Profile和Authenticate分开,就十分容易理解了。Users表本身只存储用户的Profile, 其中ID为关联不同登录方式的外键。

     id | name | birth等其他字段
    ----+------+-----------------
     A1 | Bob  |  ...
     A2 | Adam | ...
    

    而通过用户名口令登录可视为一种Authenticate的方式,利用LocalAuth表维护:

     id | user_id | username | password
    ----+---------+----------+-----------
     01 | A1      | bob      | a1b23f2c
     02 | A2      | adam     | c0932f32

    通过微博登录可视为另一种Authenticate方式,利用OAuth表维护, 但是access_token一般情况也只有几个小时的时效, 所以存储它是没有意义的, 每次登录的时候去微博后台验证一下客户端传来的token就行了。 如果用户只用了第三方登录, 那就拿第三方数据来填充刚才的User表即可。

     id | user_id | weibo_id |
    ----+---------+----------+
     11 | A1      | W-012345 |
     12 | A2      | W-234567 |
    

    如果要添加另一种OAuth登录,比如QQ登录,那就再加一个列标示不同站点也就OK了, 但是要注意用户在不同登录方式的用户名和photo一般不一样, 所以也单独存起来

     id | user_id | oauth_name | oauth_id | nick_name| photo|
    ----+---------+------------+----------+----------+------+
     11 | A1      | weibo      | W-012345 | 
     12 | A2      | weibo      | W-234567 |
     13 | A1      | qq         | Q-090807 |
     14 | A2      | qq         | Q-807060 |
    

    通过这种方式, 无论用户采用哪种方式登录, 都可以锁定到用户的user_id。

    下面再说一下网易的URS登录, 因为我们要直接采用网易通行证, 所以也就不需自己存储密码, 因此我们的架构应该设为User表

     id | user_Email | username | birth
    ----+------------+----------+-----------
     01 | aa@126.com | bob      | 
     02 | bb@126.com | adam     | 

    如果用户只用第三方登录, 显然无法填充user_Email这个字段, 因此userEmail可以为空。 如果第三方登录采用的是URS第三方的接口, 它返回的oauth_id 是aa@wx.163.com这种形式。 具体设计和上面也类似。 整体上使用这种方式比现在后台的逻辑要清晰很多

    转载于:https://www.cnblogs.com/ysq2018China/p/10291779.html

    展开全文
  • 动态树形菜单数据库表设计

    热门讨论 2010-07-27 08:04:55
    这是一篇pdf格式的论文。很详细地图文并茂地讲解了树形菜单的设计。并分析了常规设计方式的不足。以冗余数据的方式设计了高效的树形菜单
  • 数据库表设计的技巧 1. 原始单据与实体之间的关系  可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对 应且只对应一个实体。在特殊情况下,它们可能是一对多或多对一的...

    数据库表设计的技巧

    1. 原始单据与实体之间的关系
      可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对

    应且只对应一个实体。在特殊情况下,它们可能是一对多或多对一的关系,即一张原始单证对应多个实

    体,或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后,对我们设计

    录入界面大有好处。
      〖例1〗:一份员工履历资料,在人力资源信息系统中,就对应三个基本表:员工基本情况表、社会

    关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。

       2. 主键与外键
      一般而言,一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键,

    也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。
      主键与外键的设计,在全局数据库的设计中,占有重要地位。当全局数据库的设计完成以后,有个

    美国数据库设计专家说:“键,到处都是键,除了键之外,什么也没有”,这就是他的数据库设计经验

    之谈,也反映了他对信息系统核心(数据模型)的高度抽象思想。因为:主键是实体的高度抽象,主键与

    外键的配对,表示实体之间的连接。

       3. 基本表的性质
      基本表与中间表、临时表不同,因为它具有如下四个特性:
       (1) 原子性。基本表中的字段是不可再分解的。
       (2) 原始性。基本表中的记录是原始数据(基础数据)的记录。
       (3) 演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。
       (4) 稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。
      理解基本表的性质后,在设计数据库时,就能将基本表与中间表、临时表区分开来。

       4. 范式标准
      基本表及其字段之间的关系, 应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是

    最好的设计。为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间

    的目的。
      〖例2〗:有一张存放商品的基本表,如表1所示。“金额”这个字段的存在,表明该表的设计不满

    足第三范式,因为“金额”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加

    “金额”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法。


      在Rose 2002中,规定列有两种类型:数据列和计算列。“金额”这样的列被称为“计算列”,而“

    单价”和“数量”这样的列被称为“数据列”。
      表1 商品表的表结构
      商品名称 商品型号 单价 数量 金额
      电视机 29吋 2,500 40 100,000
       
       5. 通俗地理解三个范式
      通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就

    必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):
      第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
      第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
      第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余


      没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运

    行效率,就必须降低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式

    ,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。

       6. 要善于识别与正确处理多对多的关系
      若两个实体之间存在多对多的关系,则应消除这种关系。消除的办法是,在两者之间增加第三个实

    体。这样,原来一个多对多的关系,现在变为两个一对多的关系。要将原来两个实体的属性合理地分配

    到三个实体中去。这里的第三个实体,实质上是一个较复杂的关系,它对应一张基本表。一般来讲,数

    据库设计工具不能识别多对多的关系,但能处理多对多的关系。


      〖例3〗:在“图书馆信息系统”中,“图书”是一个实体,“读者”也是一个实体。这两个实体之

    间的关系,是一个典型的多对多关系:一本图书在不同时间可以被多个读者借阅,一个读者又可以借多

    本图书。为此,要在二者之间增加第三个实体,该实体取名为“借还书”,它的属性为:借还时间、借

    还标志(0表示借书,1表示还书),另外,它还应该有两个外键(“图书”的主键,“读者”的主键),使

    它能与“图书”和“读者”连接。

       7. 主键PK的取值方法
       PK是供程序员使用的表间连接工具,可以是一无物理意义的数字串, 由程序自动加1来实现。也可

    以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时,建议字段的个

    数不要太多,多了不但索引占用空间大,而且速度也慢。

       8. 正确认识数据冗余
      主键与外键在多表中的重复出现, 不属于数据冗余,这个概念必须清楚,事实上有许多人还不清楚

    。非键字段的重复出现, 才是数据冗余!而且是一种低级冗余,即重复性的冗余。高级冗余不是字段的

    重复出现,而是字段的派生出现。
      〖例4〗:商品中的“单价、数量、金额”三个字段,“金额”就是由“单价”乘以“数量”派生出

    来的,它就是冗余,而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数

    据的不一致性,因为同一数据,可能从不同时间、地点、角色上多次录入。因此,我们提倡高级冗余(派

    生性冗余),反对低级冗余(重复性冗余)。

       9. E--R图没有标准答案
      信息系统的E--R图没有标准答案,因为它的设计与画法不是惟一的,只要它覆盖了系统需求的业务

    范围和功能内容,就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案,并不意味着可以随意

    设计。好的E—R图的标准是:结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。

       10. 视图技术在数据库设计中很有用
      与基本表、代码表、中间表不同,视图是一种虚表,它依赖数据源的实表而存在。视图是供程序员

    使用数据库的一个窗口,是基表数据综合的一种形式, 是数据处理的一种方法,是用户数据保密的一种

    手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层

    视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。这样反复交迭定义, 视图的深度就

    不受限制了。
      对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统,视图的作用更加重要。这些

    系统的基本表完成物理设计之后,立即在基本表上建立第一层视图,这层视图的个数和结构,与基本表

    的个数和结构是完全相同。并且规定,所有的程序员,一律只准在视图上操作。只有数据库管理员,带

    着多个人员共同掌握的“安全钥匙”,才能直接在基本表上操作。请读者想想:这是为什么?

       11. 中间表、报表和临时表
      中间表是存放统计数据的表,它是为数据仓库、输出报表或查询结果而设计的,有时它没有主键与

    外键(数据仓库除外)。临时表是程序员个人设计的,存放临时记录,为个人所用。基表和中间表由DBA维

    护,临时表由程序员自己用程序自动维护。

       12. 完整性约束表现在三个方面
      域的完整性:用Check来实现约束,在数据库设计工具中,对字段的取值范围进行定义时,有一个Ch

    eck按钮,通过它定义字段的值城。
      参照完整性:用PK、FK、表级触发器来实现。
      用户定义完整性:它是一些业务规则,用存储过程和触发器来实现。

       13. 防止数据库设计打补丁的方法是“三少原则”
       (1) 一个数据库中表的个数越少越好。只有表的个数少了,才能说明系统的E--R图少而精,去掉了

    重复的多余的实体,形成了对客观世界的高度抽象,进行了系统的数据集成,防止了打补丁式的设计;
       (2) 一个表中组合主键的字段个数越少越好。因为主键的作用,一是建主键索引,二是做为子表的

    外键,所以组合主键的字段个数少了,不仅节省了运行时间,而且节省了索引存储空间;
       (3) 一个表中的字段个数越少越好。只有字段的个数少了,才能说明在系统中不存在数据重复,且

    很少有数据冗余,更重要的是督促读者学会“列变行”,这样就防止了将子表中的字段拉入到主表中去

    ,在主表中留下许多空余的字段。所谓“列变行”,就是将主表中的一部分内容拉出去,另外单独建一

    个子表。这个方法很简单,有的人就是不习惯、不采纳、不执行。


      数据库设计的实用原则是:在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概

    念,综合观点,不能孤立某一个原则。该原则是相对的,不是绝对的。“三多”原则肯定是错误的。试

    想:若覆盖系统同样的功能,一百个实体(共一千个属性) 的E--R图,肯定比二百个实体(共二千个属性)

    的E--R图,要好得多。
      提倡“三少”原则,是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将

    文件系统集成为应用数据库,将应用数据库集成为主题数据库,将主题数据库集成为全局综合数据库。

    集成的程度越高,数据共享性就越强,信息孤岛现象就越少,整个企业信息系统的全局E—R图中实体的

    个数、主键的个数、属性的个数就会越少。
      提倡“三少”原则的目的,是防止读者利用打补丁技术,不断地对数据库进行增删改,使企业数据

    库变成了随意设计数据库表的“垃圾堆”,或数据库表的“大杂院”,最后造成数据库中的基本表、代

    码表、中间表、临时表杂乱无章,不计其数,导致企事业单位的信息系统无法维护而瘫痪。
       “三多”原则任何人都可以做到,该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则

    是少而精的原则,它要求有较高的数据库设计技巧与艺术,不是任何人都能做到的,因为该原则是杜绝

    用“打补丁方法”设计数据库的理论依据。

       14. 提高数据库运行效率的办法
      在给定的系统硬件和系统软件条件下,提高数据库系统的运行效率的办法是:
       (1) 在数据库物理设计时,降低范式,增加冗余, 少用触发器, 多用存储过程。
       (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条),复杂计算要先在数据库外面,以

    文件系统方式用C++语言计算处理完成之后,最后才入库追加到表中去。这是电信计费系统设计的经验。
       (3) 发现某个表的记录太多,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,

    以该表主键PK的某个值为界线,将该表的记录水平分割为两个表。若发现某个表的字段太多,例如超过

    八十个,则垂直分割该表,将原来的一个表分解为两个表。
       (4) 对数据库管理系统DBMS进行系统优化,即优化各种系统参数,如缓冲区个数。
       (5) 在使用面向数据的SQL语言进行程序设计时,尽量采取优化算法。
      总之,要提高数据库的运行效率,

    必须从

    • 数据库系统级优化、
    • 数据库设计级优化、
    • 程序实现级优化

    ,这三个层次上同时下功夫。

      上述十四个技巧,是许多人在大量的数据库分析与设计实践中,逐步总结出来的。对于这些经验的

    运用,读者不能生帮硬套,死记硬背,而要消化理解,实事求是,灵活掌握。并逐步做到:在应用中发

    展,在发展中应用。

    展开全文
  • 常见电商项目的数据库表设计(MySQL版)

    万次阅读 多人点赞 2019-03-14 11:17:06
    电商常用功能模块的数据库设计 常见问题的数据库解决方案 环境: MySQL5.7 图形客户端,SQLyog Linux 模块: 用户:注册、登陆 商品:浏览、管理 订单:生成、管理 仓配:库存、管理 电商实例数据库结构设计: ...

    常见电商项目的数据库表设计(MySQL版)

    简介:

    目的:

    • 电商常用功能模块的数据库设计
    • 常见问题的数据库解决方案

    环境:

    • MySQL5.7
    • 图形客户端,SQLyog
    • Linux

    模块:

    • 用户:注册、登陆
    • 商品:浏览、管理
    • 订单:生成、管理
    • 仓配:库存、管理

    电商实例数据库结构设计:

    • 电商项目用户模块

    • 用户表涉及的实体
      在这里插入图片描述

    • 改进1:第三范式:将依赖传递的列分离出来。比如:登录名<-用户级别<-级别积分上限,级别积分下限
      在这里插入图片描述

    • 改进2:尽量做到冷热数据的分离,减小表的宽度
      在这里插入图片描述

    • 用户登录表(customer_login)
      CREATE TABLE customer_login(
      customer_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘用户ID’,
      login_name VARCHAR(20) NOT NULL COMMENT ‘用户登录名’,
      password CHAR(32) NOT NULL COMMENT ‘md5加密的密码’,
      user_stats TINYINT NOT NULL DEFAULT 1 COMMENT ‘用户状态’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_customerid(customer_id)
      ) ENGINE = innodb COMMENT ‘用户登录表’

    • 用户信息表(customer_inf)
      CREATE TABLE customer_inf(
      customer_inf_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘自增主键ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘customer_login表的自增ID’,
      customer_name VARCHAR(20) NOT NULL COMMENT ‘用户真实姓名’,
      identity_card_type TINYINT NOT NULL DEFAULT 1 COMMENT ‘证件类型:1 身份证,2 军官证,3 护照’,
      identity_card_no VARCHAR(20) COMMENT ‘证件号码’,
      mobile_phone INT UNSIGNED COMMENT ‘手机号’,
      customer_email VARCHAR(50) COMMENT ‘邮箱’,
      gender CHAR(1) COMMENT ‘性别’,
      user_point INT NOT NULL DEFAULT 0 COMMENT ‘用户积分’,
      register_time TIMESTAMP NOT NULL COMMENT ‘注册时间’,
      birthday DATETIME COMMENT ‘会员生日’,
      customer_level TINYINT NOT NULL DEFAULT 1 COMMENT ‘会员级别:1 普通会员,2 青铜,3白银,4黄金,5钻石’,
      user_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘用户余额’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_customerinfid(customer_inf_id)
      ) ENGINE = innodb COMMENT ‘用户信息表’;

    • 用户级别表(customerlevelinf)
      CREATE TABLE customer_level_inf(
      customer_level TINYINT NOT NULL AUTO_INCREMENT COMMENT ‘会员级别ID’,
      level_name VARCHAR(10) NOT NULL COMMENT ‘会员级别名称’,
      min_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘该级别最低积分’,
      max_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘该级别最高积分’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_levelid(customer_level)
      ) ENGINE = innodb COMMENT ‘用户级别信息表’;

    • 用户地址表(customer_addr)
      CREATE TABLE customer_addr(
      customer_addr_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘自增主键ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘customer_login表的自增ID’,
      zip SMALLINT NOT NULL COMMENT ‘邮编’,
      province SMALLINT NOT NULL COMMENT ‘地区表中省份的ID’,
      city SMALLINT NOT NULL COMMENT ‘地区表中城市的ID’,
      district SMALLINT NOT NULL COMMENT ‘地区表中的区ID’,
      address VARCHAR(200) NOT NULL COMMENT ‘具体的地址门牌号’,
      is_default TINYINT NOT NULL COMMENT ‘是否默认’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_customeraddid(customer_addr_id)
      ) ENGINE = innodb COMMENT ‘用户地址表’;

    • 用户积分日志表(customerpointlog)
      CREATE TABLE customer_point_log(
      point_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘积分日志ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘用户ID’,
      source TINYINT UNSIGNED NOT NULL COMMENT ‘积分来源:0订单,1登陆,2活动’,
      refer_number INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘积分来源相关编号’,
      change_point SMALLINT NOT NULL DEFAULT 0 COMMENT ‘变更积分数’,
      create_time TIMESTAMP NOT NULL COMMENT ‘积分日志生成时间’,
      PRIMARY KEY pk_pointid(point_id)
      ) ENGINE = innodb COMMENT ‘用户积分日志表’;

    • 用户余额变动表(customerbalancelog)
      CREATE TABLE customer_balance_log(
      balance_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘余额日志ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘用户ID’,
      source TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT ‘记录来源:1订单,2退货单’,
      source_sn INT UNSIGNED NOT NULL COMMENT ‘相关单据ID’,
      create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘记录生成时间’,
      amount DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘变动金额’,
      PRIMARY KEY pk_balanceid(balance_id)
      ) ENGINE = innodb COMMENT ‘用户余额变动表’;

    • 用户登陆日志表(customerloginlog)
      CREATE TABLE customer_login_log(
      login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘登陆日志ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘登陆用户ID’,
      login_time TIMESTAMP NOT NULL COMMENT ‘用户登陆时间’,
      login_ip INT UNSIGNED NOT NULL COMMENT ‘登陆IP’,
      login_type TINYINT NOT NULL COMMENT ‘登陆类型:0未成功,1成功’,
      PRIMARY KEY pk_loginid(login_id)
      ) ENGINE = innodb COMMENT ‘用户登陆日志表’;

    Hash分区表

    分区表特点:逻辑上为一个表,在物理上存储在多个文件中

    CREATE TABLE customer_login_log(
    login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘登陆日志ID’,
    customer_id INT UNSIGNED NOT NULL COMMENT ‘登陆用户ID’,
    login_time TIMESTAMP NOT NULL COMMENT ‘用户登陆时间’,
    login_ip INT UNSIGNED NOT NULL COMMENT ‘登陆IP’,
    login_type TINYINT NOT NULL COMMENT ‘登陆类型:0未成功,1成功’,
    PRIMARY KEY pk_loginid(login_id)
    ) ENGINE = innodb COMMENT ‘用户登陆日志表’
    PARTITION BY HASH(customer_id) PARTITIONS 4;
    区别就在于加了 PARTITION这个命令。 文件结构上的区别

    普通表结构:

    • customer_login_log.frm
    • customer_login_log.ibd

    分区表结构:

    • customer_login_log.frm
    • customer_login_log#P#p0.ibd
    • customer_login_log#P#p1.ibd
    • customer_login_log#P#p2.ibd
    • customer_login_log#P#p3.ibd

    按HASH分区的特点

    • 根据MOD(分区建,分区数)的值把数据行存储到表的不同分区
    • 数据可以平均的分布在各个分区中
    • HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型比如
    • UNIX_TIMESTAMP(login_time)

    Range分区表

    特点:

    • 根据分区键值的范围把数据行存储到表的不同分区中
    • 多个分区的范围要连续,但是不能重复
    • 默认情况下使用VALUES LESS THAN属性,即每个分区不包括指定的那个值

    CREATE TABLE customer_login_log(
    login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘登陆日志ID’,
    customer_id INT UNSIGNED NOT NULL COMMENT ‘登陆用户ID’,
    login_time TIMESTAMP NOT NULL COMMENT ‘用户登陆时间’,
    login_ip INT UNSIGNED NOT NULL COMMENT ‘登陆IP’,
    login_type TINYINT NOT NULL COMMENT ‘登陆类型:0未成功,1成功’,
    PRIMARY KEY pk_loginid(login_id)
    ) ENGINE = innodb COMMENT ‘用户登陆日志表’
    PARTITION BY RANGE (customer_id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (10000),
    PARTITION p2 VALUES LESS THAN (10000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
    );

    Range分区的适用范围

    • 分区键为日期或是时间类型
    • 所有SELECT查询中都包括分区键

    LIST分区

    特点:

    • 按分区键取值的列表进行分区
    • 同范围分区一样,各分区的列表值不能重复
    • 每一行数据必须能找到对应的分区列表,否则数据插入失败

    CREATE TABLE customer_login_log(
    login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘登陆日志ID’,
    customer_id INT UNSIGNED NOT NULL COMMENT ‘登陆用户ID’,
    login_time TIMESTAMP NOT NULL COMMENT ‘用户登陆时间’,
    login_ip INT UNSIGNED NOT NULL COMMENT ‘登陆IP’,
    login_type TINYINT NOT NULL COMMENT ‘登陆类型:0未成功,1成功’,
    PRIMARY KEY pk_loginid(login_id)
    ) ENGINE = innodb COMMENT ‘用户登陆日志表’
    PARTITION BY LIST (login_type) (
    PARTITION p0 VALUES (1,3,5,7,9),
    PARTITION p1 VALUES (2,4,6,8)
    );
    如何选择正确的分区类型
    如何为customerloginlog表分区

    业务场景:

    • 用户每次登录都会记录
    • 日志保存一年,一年后可删除

    解决:

    • 使用RANGE范围分区
    • 以login_type作为分区键

    如何查看分区是否正确:

    • 使用SELECT查询 information_schema.PARTITIONS
    • 这里不使用MAXVALUE,防止后续的日期全部归到一个分区中,而是使用定时计划修改
    • 增加分区 ALTER TABLE customer_login_log ADD PARTITION(PARTITION p4 VALUESLESS THAN(2018))
    • 删除以前一年的分区 ALTER TABLE customer_login_log DROP PARTITION p0;
    • 过期数据归档
      1.建立用户登陆日志归档 CREATE TABLE arch_customer_login_log(login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT’登陆日志ID’,customer_id INT UNSIGNED NOT NULL COMMENT’登陆用户ID’,login_time TIMESTAMP NOT NULL COMMENT’用户登陆时间’,login_ip INT UNSIGNED NOT NULL COMMENT’登陆IP’,login_type TINYINT NOT NULL COMMENT’登陆类型:0未成功,1成功’,PRIMARY KEY pk_loginid(login_id))ENGINE=innodb COMMENT’用户登陆日志归档表’
      2.归档操作: ALTER TABLE customer_login_log EXCHANGE PARTITION p1 WITH TABLE arch_customer_login_log
      3.迁移后删除: ALTER TABLE customer_login_log DROP PARTITION p2
      4.根据需要可以把归档的表引擎改为 ARCHIVE
      5.分区数据归档迁移条件
      6.操作步骤
        mysql >= 5.7
        结构相同
        归档到的数据表一定是非分区表
        非临时表;不能有外键约束
        归档引擎要是:archive

    使用分区表的注意事项

    • 结合业务场景选择分区键,避免跨分区查询
    • 对分区表进行查询最好在WHERE从句中包含分区键
    • 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分

    商品实体
    在这里插入图片描述

    • 品牌信息表(brand_info)
      CREATE TABLE brand_info(
      brand_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘品牌ID’,
      brand_name VARCHAR(50) NOT NULL COMMENT ‘品牌名称’,
      telephone VARCHAR(50) NOT NULL COMMENT ‘联系电话’,
      brand_web VARCHAR(100) COMMENT ‘品牌网络’,
      brand_logo VARCHAR(100) COMMENT ‘品牌logo URL’,
      brand_desc VARCHAR(150) COMMENT ‘品牌描述’,
      brand_status TINYINT NOT NULL DEFAULT 0 COMMENT ‘品牌状态,0禁用,1启用’,
      brand_order TINYINT NOT NULL DEFAULT 0 COMMENT ‘排序’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_brandid (brand_id)
      )ENGINE=innodb COMMENT ‘品牌信息表’;

    • 分类信息表(product_category)
      CREATE TABLE product_category(
      category_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘分类ID’,
      category_name VARCHAR(10) NOT NULL COMMENT ‘分类名称’,
      category_code VARCHAR(10) NOT NULL COMMENT ‘分类编码’,
      parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘父分类ID’,
      category_level TINYINT NOT NULL DEFAULT 1 COMMENT ‘分类层级’,
      category_status TINYINT NOT NULL DEFAULT 1 COMMENT ‘分类状态’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_categoryid(category_id)
      )ENGINE=innodb COMMENT ‘商品分类表’

    • 供应商信息表(supplier_info)
      CREATE TABLE supplier_info(
      supplier_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘供应商ID’,
      supplier_code CHAR(8) NOT NULL COMMENT ‘供应商编码’,
      supplier_name CHAR(50) NOT NULL COMMENT ‘供应商名称’,
      supplier_type TINYINT NOT NULL COMMENT ‘供应商类型:1.自营,2.平台’,
      link_man VARCHAR(10) NOT NULL COMMENT ‘供应商联系人’,
      phone_number VARCHAR(50) NOT NULL COMMENT ‘联系电话’,
      bank_name VARCHAR(50) NOT NULL COMMENT ‘供应商开户银行名称’,
      bank_account VARCHAR(50) NOT NULL COMMENT ‘银行账号’,
      address VARCHAR(200) NOT NULL COMMENT ‘供应商地址’,
      supplier_status TINYINT NOT NULL DEFAULT 0 COMMENT ‘状态:0禁止,1启用’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_supplierid(supplier_id)
      ) ENGINE = innodb COMMENT ‘供应商信息表’;

    • 商品信息表(product_info)
      宽度较宽,字段差不多一起使用
      可以被缓存
      CREATE TABLE product_info(
      product_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘商品ID’,
      product_core CHAR(16) NOT NULL COMMENT ‘商品编码’,
      product_name VARCHAR(20) NOT NULL COMMENT ‘商品名称’,
      bar_code VARCHAR(50) NOT NULL COMMENT ‘国条码’,
      brand_id INT UNSIGNED NOT NULL COMMENT ‘品牌表的ID’,
      one_category_id SMALLINT UNSIGNED NOT NULL COMMENT ‘一级分类ID’,
      two_category_id SMALLINT UNSIGNED NOT NULL COMMENT ‘二级分类ID’,
      three_category_id SMALLINT UNSIGNED NOT NULL COMMENT ‘三级分类ID’,
      supplier_id INT UNSIGNED NOT NULL COMMENT ‘商品的供应商ID’,
      price DECIMAL(8,2) NOT NULL COMMENT ‘商品销售价格’,
      average_cost DECIMAL(18,2) NOT NULL COMMENT ‘商品加权平均成本’,
      publish_status TINYINT NOT NULL DEFAULT 0 COMMENT ‘上下架状态:0下架1上架’,
      audit_status TINYINT NOT NULL DEFAULT 0 COMMENT ‘审核状态:0未审核,1已审核’,
      weight FLOAT COMMENT ‘商品重量’,
      length FLOAT COMMENT ‘商品长度’,
      height FLOAT COMMENT ‘商品高度’,
      width FLOAT COMMENT ‘商品宽度’,
      color_type ENUM(‘红’,‘黄’,‘蓝’,‘黑’),
      production_date DATETIME NOT NULL COMMENT ‘生产日期’,
      shelf_life INT NOT NULL COMMENT ‘商品有效期’,
      descript TEXT NOT NULL COMMENT ‘商品描述’,
      indate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘商品录入时间’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_productid(product_id)
      ) ENGINE = innodb COMMENT ‘商品信息表’;

    • 商品图片表(productpicinfo)
      CREATE TABLE product_pic_info(
      product_pic_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘商品图片ID’,
      product_id INT UNSIGNED NOT NULL COMMENT ‘商品ID’,
      pic_desc VARCHAR(50) COMMENT ‘图片描述’,
      pic_url VARCHAR(200) NOT NULL COMMENT ‘图片URL’,
      is_master TINYINT NOT NULL DEFAULT 0 COMMENT ‘是否主图:0.非主图1.主图’,
      pic_order TINYINT NOT NULL DEFAULT 0 COMMENT ‘图片排序’,
      pic_status TINYINT NOT NULL DEFAULT 1 COMMENT ‘图片是否有效:0无效 1有效’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_picid(product_pic_id)
      )ENGINE=innodb COMMENT ‘商品图片信息表’;

    • 商品评论表(product_comment)
      CREATE TABLE product_comment(
      comment_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT ‘评论ID’,
      product_id INT UNSIGNED NOT NULL COMMENT ‘商品ID’,
      order_id BIGINT UNSIGNED NOT NULL COMMENT ‘订单ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘用户ID’,
      title VARCHAR(50) NOT NULL COMMENT ‘评论标题’,
      content VARCHAR(300) NOT NULL COMMENT ‘评论内容’,
      audit_status TINYINT NOT NULL COMMENT ‘审核状态:0未审核,1已审核’,
      audit_time TIMESTAMP NOT NULL COMMENT ‘评论时间’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_commentid(comment_id)
      ) ENGINE = innodb COMMENT ‘商品评论表’;

    订单模块
    在这里插入图片描述

    • 订单主表(order_master)
      CREATE TABLE order_master(
      order_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘订单ID’,
      order_sn BIGINT UNSIGNED NOT NULL COMMENT ‘订单编号 yyyymmddnnnnnnnn’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘下单人ID’,
      shipping_user VARCHAR(10) NOT NULL COMMENT ‘收货人姓名’,
      province SMALLINT NOT NULL COMMENT ‘省’,
      city SMALLINT NOT NULL COMMENT ‘市’,
      district SMALLINT NOT NULL COMMENT ‘区’,
      address VARCHAR(100) NOT NULL COMMENT ‘地址’,
      payment_method TINYINT NOT NULL COMMENT ‘支付方式:1现金,2余额,3网银,4支付宝,5微信’,
      order_money DECIMAL(8,2) NOT NULL COMMENT ‘订单金额’,
      district_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘优惠金额’,
      shipping_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘运费金额’,
      payment_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘支付金额’,
      shipping_comp_name VARCHAR(10) COMMENT ‘快递公司名称’,
      shipping_sn VARCHAR(50) COMMENT ‘快递单号’,
      create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘下单时间’,
      shipping_time DATETIME COMMENT ‘发货时间’,
      pay_time DATETIME COMMENT ‘支付时间’,
      receive_time DATETIME COMMENT ‘收货时间’,
      order_status TINYINT NOT NULL DEFAULT 0 COMMENT ‘订单状态’,
      order_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘订单积分’,
      invoice_time VARCHAR(100) COMMENT ‘发票抬头’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_orderid(order_id)
      )ENGINE = innodb COMMENT ‘订单主表’;

    • 订单详情表(order_detail)
      CREATE TABLE order_detail(
      order_detail_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘订单详情表ID’,
      order_id INT UNSIGNED NOT NULL COMMENT ‘订单表ID’,
      product_id INT UNSIGNED NOT NULL COMMENT ‘订单商品ID’,
      product_name VARCHAR(50) NOT NULL COMMENT ‘商品名称’,
      product_cnt INT NOT NULL DEFAULT 1 COMMENT ‘购买商品数量’,
      product_price DECIMAL(8,2) NOT NULL COMMENT ‘购买商品单价’,
      average_cost DECIMAL(8,2) NOT NULL COMMENT ‘平均成本价格’,
      weight FLOAT COMMENT ‘商品重量’,
      fee_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘优惠分摊金额’,
      w_id INT UNSIGNED NOT NULL COMMENT ‘仓库ID’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_orderdetailid(order_detail_id)
      )ENGINE = innodb COMMENT ‘订单详情表’

    • 购物车表(order_cart)
      CREATE TABLE order_cart(
      cart_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘购物车ID’,
      customer_id INT UNSIGNED NOT NULL COMMENT ‘用户ID’,
      product_id INT UNSIGNED NOT NULL COMMENT ‘商品ID’,
      product_amount INT NOT NULL COMMENT ‘加入购物车商品数量’,
      price DECIMAL(8,2) NOT NULL COMMENT ‘商品价格’,
      add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘加入购物车时间’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_cartid(cart_id)
      ) ENGINE = innodb COMMENT ‘购物车表’;

    • 仓库信息表(warehouse_info)
      CREATE TABLE warehouse_info(
      w_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘仓库ID’,
      warehouse_sn CHAR(5) NOT NULL COMMENT ‘仓库编码’,
      warehoust_name VARCHAR(10) NOT NULL COMMENT ‘仓库名称’,
      warehouse_phone VARCHAR(20) NOT NULL COMMENT ‘仓库电话’,
      contact VARCHAR(10) NOT NULL COMMENT ‘仓库联系人’,
      province SMALLINT NOT NULL COMMENT ‘省’,
      city SMALLINT NOT NULL COMMENT ‘市’,
      distrct SMALLINT NOT NULL COMMENT ‘区’,
      address VARCHAR(100) NOT NULL COMMENT ‘仓库地址’,
      warehouse_status TINYINT NOT NULL DEFAULT 1 COMMENT ‘仓库状态:0禁用,1启用’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_wid(w_id)
      )ENGINE = innodb COMMENT ‘仓库信息表’;

    • 商品库存表(warehouse_product)
      CREATE TABLE warehouse_product(
      wp_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘商品库存ID’,
      product_id INT UNSIGNED NOT NULL COMMENT ‘商品ID’,
      w_id SMALLINT UNSIGNED NOT NULL COMMENT ‘仓库ID’,
      current_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘当前商品数量’,
      lock_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘当前占用数据’,
      in_transit_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘在途数据’,
      average_cost DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘移动加权成本’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_wpid(wp_id)
      )ENGINE = innodb COMMENT ‘商品库存表’

    • 物流公司信息表(shipping_info)
      CREATE TABLE shipping_info(
      ship_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
      ship_name VARCHAR(20) NOT NULL COMMENT ‘物流公司名称’,
      ship_contact VARCHAR(20) NOT NULL COMMENT ‘物流公司联系人’,
      telephone VARCHAR(20) NOT NULL COMMENT ‘物流公司联系电话’,
      price DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT ‘配送价格’,
      modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
      PRIMARY KEY pk_shipid(ship_id)
      )ENGINE = innodb COMMENT ‘物流公司信息表’;

    DB规划

    • 为以后数据库迁移提供方便
    • 避免跨库操作,把经常一起关联查询的表放到一个DB中
    • 为方便识别表所在的DB,在表名前增加库名前缀

    用户数据库(mccustomerdb)

    • customerinf
    • customer_login
    • customerlevelinf
    • customerloginlog
    • customerpointlog
    • customerbalancelog

    商品数据库(mcproductdb)

    • productinfo
    • productpicinfo
    • productcategory
    • productsupplierinfo
    • productcomment
    • productbrandinfo

    订单数据库(mcorderdb)

    • ordermaster
    • orderdetail
    • ordercustomeraddr
    • ordercart
    • shippinginfo
    • warehouseinfo
    • warehouse_product
    展开全文
  • 数据库表设计 基本思路

    千次阅读 2018-01-30 16:05:06
    好的数据库表设计会影响数据库操作效率。特别是数据多的时候,如果的结构不好的话操作的时候条件(where后的内容)会变的非常复杂。 SQL是关系数据库中用到的一种语言。所以,为了简化SQL,的关系(内部和外部...
  • 消息系统数据库表设计

    千次阅读 2019-06-14 18:16:10
    初步设计消息系统,用户的字段待完善;这里把分组和群组放一张用类型区分
  • 用户注册推荐数据库表设计

    千次阅读 2019-02-26 16:47:36
    如果你有这个习惯,那就来看看下面的例子吧,我将描述一个产品业务的增量迭代过程,你会看到开发先于设计带来的麻烦。 二、业务描述和前提 业务 描述 推荐功能v1.1 用户注册时填写推荐人的推荐码,后台cms...
  • Navicat导出数据库表设计文档

    千次阅读 2020-04-09 20:57:11
    1、打开navicat,新建查询 2、输入以下SQL...设计表,就可以看到 4、导出为文档(毕业设计可用) 找到上方的导出 选择输出为doc 添加你想要导出的路径 后面-----下一步-----开始,就行了 打开你的文档,已经生成好了!
  • FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'crm' // crm =数据库名 AND table_name = 'sys_user' // sys_user =表名 剩下的属性: 上述SQL执行后: 然后点击“导出表格”: 然后: 然后: 下一步...
  • Oracle 数据库设计-数据库设计

    千次阅读 2018-11-01 21:22:57
    数据库设计中,我的工作中经常会分析怎样商业逻辑中的表格如何设计。再设计表的关系之前 需要先了解关系型数据库特点 1、关系型数据库,是指采用了关系模型来组织数据的数据库; 2、关系型数据库的最大特点就是...
  • 订单审批流程 数据库表设计

    万次阅读 2018-10-23 17:06:55
    2、审批类型 字段名称 字段类型 字段长度 主键 非空 注释 APPROVALCODE VARCHAR2 2 Y NOT NULL 审批类型编码 APPROVALTYPE VARCHAR2 30 Y NOT NULL 审批类型 3、审批...
  • 数据库表设计的几点原则

    千次阅读 2018-11-03 10:25:18
    前言:数据库设计在平时的工作是必不可少的,良好的设计可以让我们查询效率更高,加快网站访问速度,提升用户体验,并且方便于我们查询数据。本篇博客就来聚焦一下,如何设计出高可复用,优良的结构,从而在实际...
  • Mysql 数据库表设计 应该注意什么?

    千次阅读 2020-07-13 17:00:58
    良好的逻辑设计和物理设计是高性能的基石,在进行设计的时候应该根据系统将要执行的查询语句来设计表,这往往需要权衡各种因素,那么这些因素有哪些呢?我们应该重点关注什么呢?这常常苦恼着我们,良好的设计原则是...
  • 商城 商品模块 数据库 表设计

    万次阅读 多人点赞 2017-05-12 23:30:04
    要实现一个商城,对于商品模块中的数据库表设计不懂,主要是:相同类别的产品的产品参数相同,不同类别的不同,这里就不懂要怎么设计了,所以上网找几篇博客了解什么是SPUSKUARPU PHP商城 商品模块 数据库 表设计...
  • 会员 数据库表设计

    万次阅读 2016-11-25 17:14:14
    http://www.cnblogs.com/wuhuacong/p/3957428.html
  • 公告(通告),消息,提醒等基本功能数据库设计1.公告announcement: 平台发,用户收.分实时公告和非实时公告.优先级:紧急,高,普通. 平台可以向单个用户发,可以向多个用户发,可以向某一用户类型发,可以向全部用户发. 公告...
  • 1.数据库表设计,共分为两部分,第一部分为商品管理,第二部分为用户管理 具体关系如下: 商品系列: 用户系列: 2.项目效果展示 (1)登录页面 (2) 总界面 (3)用户管理 (4)...
  • 无限级分销系统数据库表设计

    千次阅读 2018-12-16 21:18:00
    这次分享的内容很简单,就是一张设计思路。 一、背景 在做交易所的时候有个需求,需要统计邀请注册人数,比如 A邀请B, B邀请C, C邀请D。那么A的邀请人数就是3个,B的邀请人数就是2个,C的邀请人数就是1个。除...
  • 聊天软件开发_3_数据库表设计

    千次阅读 2019-06-28 14:53:10
    采用MySQL8.0数据库,建立数据库user,并在其中建立用户账号信息info,好友关系friend,群组关系 一、基本描述 系统拥有多个用户,每个用户拥有一个账号,用户基本信息包括姓名、昵称、个性签名、邮箱、...
  • 结构: CREATE TABLE `sign_in` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `user_id` bigint(20) NOT NULL COMMENT '签到用户id', `continue_days` int(3) NOT NULL DEFAULT '1' COMMENT '...
  • 树形结构的数据库表设计

    万次阅读 2016-07-30 18:20:20
    树形结构的数据库表设计 最近研究树形菜单网上找了很多例子看了。一下是网上找的一些资料,然后自己重新实践,记录下免得下次又忘记了。 程序设计过程中,我们常常用树形结构来表征某些数据的关联关系,如企业上下级...
  • 权限数据库表设计

    千次阅读 2017-06-27 22:27:10
    设计思想 设计实例 权限permission 英文描述 类型 长度 是否主键 备注 id int 11 Y 主键 name string 255 N 权限名 column_id i
  • 电商秒杀系统-数据库表设计

    万次阅读 2016-07-11 15:51:09
    电商秒杀系统之数据库表设计 好长时间没有接触到数据库这块,通过一个简单电商秒杀系统回顾以前在项目中用到的一些技术 简单电商秒杀系统这里我们使用的数据库是mysql 这里有几点说明: engine: mysql...
  • 1引言 4 1.1编写目的 4 1.2背景 5 1.3定义 5 1.4参考资料 6 2数据库物理模型 7 2.1整体设计 7 2.2角色与权限管理 7 2.3消息管理 9 2.4用户信息 10 2.5分站信息 12 2.6备份计划 13 2.7备份文件 14
  • 数据库表设计(一对多,多对多)

    万次阅读 多人点赞 2018-09-26 09:21:05
    三、数据库怎么设计多对多的数据   回到顶部 一、总结 1、项目中小组和主题之间是多对多的 2、可以将常用信息和不常用信息分成两个来优化数据库  2、一对多的话:在多的里面建立一的字段,比如母亲(一...
  • 常见电商项目的数据库表设计

    千次阅读 2019-05-31 15:04:04
    名称 链接 常见电商项目的数据库表设计(MySQL版) https://cloud.tencent.com/developer/article/1164332

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,519,104
精华内容 607,641
关键字:

数据库表设计

友情链接: bluethooth-master.zip