旅游项目之数据库表的设计_旅游数据库设计

一、数据库表的设计思路

1、从业务出发

不应该针对整个系统进行数据库表的设计，而应该根据系统架构中的组件划分，针对每个组件所处理的业务进行组件单元的数据库设计；不同组件间对应的数据库表之间的关联尽可能少，如果不同组件间的表需要外键关联也尽量不要创建外键关联，而只是记录关联表的一个主键，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。
就比如针对用户模块，去设计用户表，用户表里面有用户名、密码、电话号码、创建时间、更新时间等字段。用户模块还有一个很重要的实现功能也就是权限角色表来应用于登陆校验权限校验部分，而关于这方面就会有权限表、角色表、角色权限表，用于记录某用户分别有什么角色或者权限去访问某一块的功能。还有一些产品用户收藏表、旅拍用户收藏表。
针对商品模块，就会有商品表，商品表里面有酒店名、价格、详细描述、产品浏览数、发布状态、产品分类id等字段。还会有订单收藏夹表，用于关联用户和商品之间的收藏与被收藏关系。另外还会有一个产品分类表，因为分开表存储方便用户进行搜索，也减少了搜索时间。
针对订单模块，会有订单表，订单详情表，而订单表就是简短的记录了订单号、订单价格、支付方式等字段。订单详情表与订单表关联起来，存储更多的关于这个订单的更多信息，例如购买人、商品描述、交易号等信息。

2、领域模型驱动法

采用领域模型驱动的方式和自顶向下的思路进行数据库的设计，首先分析系统业务，根据职责定义对象。对象要符合封装的特性，确保与职责相关的数据项被定义在一个对象之内，这些数据项能够完整描述该职责，不会出现职责描述缺失。并且一个对象有且只有一个职责，如果一个对象要负责两个或两个以上的职责，应进行拆分。
自顶向下就是先确定数据流图，比如要输入什么数据，相对应的会产生什么数据，其实就是确定业务逻辑，再根据每个模块的具体职责去定义对象，比如用户模块还要去用于验证登录，所以就会相对应的产生出权限表角色表等。

3、数据库表的映射

根据建立的领域模型进行数据库表的映射，此时应参考数据库设计第二范式：一个表中的所有非关键字属性都依赖于整个关键字。关键字可以是一个属性，也可以是多个属性的集合，不论何种方式，都应确保关键字能够保证唯一性。在确定关键字时，应保证关键字不会参与业务且不会出现更新异常，最优解决方案为采用一个自增数值型属性或一个随机字符串作为表的关键字。（主键：id）

4、建立索引

应针对所有表的主键和外键建立索引，有针对性的（针对一些大数据量和常用检索方式）建立组合属性的索引，提高检索效率。虽然建立索引会消耗部分系统资源，但比较起在检索时搜索整张表中的数据尤其时表中的数据量较大时所带来的性能影响，以及无索引时的排序操作所带来的性能影响，这种方式仍然是值得提倡的。

注：三个范式
第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。（没有冗余的数据库设计可以做到。但没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。）

二、数据库表的设计原则

1、主键与外键

一般而言，一个实体不能既无主键又无外键。在E-R 图中，处于叶子部位的实体，可以定义主键，也可以不定义主键 ,但必须要有外键(因为它有父亲)。
主键与外键的设计，在全局数据库的设计中，占有重要地位。主键是实体的高度抽象，主键与外键的配对，表示实体之间的连接。

2、范式标准

基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。
为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

〖例〗：
有一张存放商品的基本表，“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段反而可以使查询效率更快。

3、防止数据库设计打补丁的方法是“三少原则”

(1) 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E–R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计；
(2) 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间；
(3) 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行。 数据库设计的实用原则：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念，综合观点，不能孤立某一个原则。该原则是相对的，而不是绝对的。

4、提高数据库运行效率的方法

在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
(1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
(2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。
(3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键 PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则 垂直分割该表，将原来的一个表分解为两个表。
(4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
(5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化三个层次上同时下功夫。

5、数据库的运行与维护

数据库运行与维护的主要任务包括：
A、维护数据库的安全性与完整性
B、监测并改善数据库性能
C、重新组织和构造数据库
只有数据库系统在运行，就需要不断地进行修改、调整和维护。

三、数据库设计规范

1、规范化与反规范化

规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度，而且范式越高性能就会越差。出于性能和方便管理的考虑，原则上表设计应满足第三范式。有时为了提高某些查询或应用的性能而可以破坏规范规则，即反规范化。

数据应当按两种类别进行组织：频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据，内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。比较复杂的方法是将规范化的表作为逻辑数据库设计的基础，然后再根据整个应用系统的需要，物理地非规范化数据。

2、字段设计规范

（1）一般来说，应该使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。
（2）选择更简单的数据类型。例如，比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。
（3）尽可能把字段定义为NOT NULL。对于字段能否NULL，应该在SQL建表脚本中明确指明，不应使用缺省。
（4）一个表中的字段不要太多，理论上不要超过80个。
（5）数据库中所有布尔型中数值0表示为假；数值1表示为真
（6）当字段定义为字符串类型时使用VARCHAR2而不用NVARCHAR
（7）字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值为数值0

3、键设计规范

（1）为关联字段创建外键。
（2）所有的键都必须唯一。
（3）尽可能避免使用复合键。
（4）外键总是关联唯一的键字段。
（5）尽可能使用系统生成（如序列SEQUENCE产生）的主键。
（6）可选键有时可做主键。
（7）一个表中组合主键的字段个数尽可能少。

4、索引设计规范

（1）如果一列出现在表达式或函数中，不会使用该列上的索引
（2）要索引外键
（3）对于索引选择性高的列使用B-Tree索引
（4）对于索引选择性低的列使用位图索引
（5）HASH索引只适用于相等比较
（6）不要索引大型字段（有很多字符的字段）
（7）不要索引常用的小型表

5、SQL语句设计规范

（1）字符类型数据
SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字符串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用TRIM(),LOWER()等函数格式化匹配条件。

（2）复杂SQL
对于非常复杂的SQL（特别是有多层嵌套，带子句或相关子查询的），应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

（3）避免IN子句
使用 IN 或 NOT IN 子句时，特别是当子句中有多个值且表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。

（4）避免使用SELECT * 语句
如果不必要取出所有数据，不要用 * 来代替，应给出字段列表。

（5）避免不必要的排序
不必要的数据排序大大的降低系统性能。

（6）INSERT语句
使用INSERT语句一定要给出插入值的字段列表，这样即使表加了字段也不会影响现有系统的运行。

（7）多表连接
做多表操作时，应该给每个表取一个别名，每个表字段都应该标明其所属哪个表。

（8）参数的传递
SQL语句的编写，变量尽量使用“?”绑定变量。

四、项目中的具体实施步骤

1、确定业务逻辑

先按照现实世界要处理的对象（用户、商品）进行详细调查，采用自顶向下的分析方法，确定业务系统的实现逻辑。

2、画E-R图

画出设计数据库的E-R模型图，确认需求信息的正确和完整。具体来说就是从需求分析中找到实体，确认实体的属性、确认实体的关系，画出E-R图。
步骤：
（1）三个实体对应局部E-R图：

（2）将局部E-R图合并成为全局E-R模型：
优化：
消除初步E-R图中不必要的冗余，生成基本的E-R图。
冗余数据：可由基本的数据导出的数据。
冗余联系：可由基本的联系导出的联系。
（3）将三个实体合并之后，就要考虑三者之间的关系了。比如用户下了单，产生了订单，那订单数据库里面就应该有用户表的字段（用户名、用户id）等等。

3、pdMAN建模

使用pdMAN进行数据库建模，初步制定具体数据库表，以及各数据库表之间的关联关系。确定各字段名的类型、字段长度，是否采用主键或外键、约束等。（参考上面的字段设计规范）

4、编写sql语句

接下来就是写sql语句创建数据库表了。（参考上面的sql语句设计规范）

5、测试与试运行

要根据逻辑设计和物理设计的结果，在计算机建立起实际的数据库结构、装入数据、进行测试和试运行

6、对表的某些字段加索引

（1）索引创建原则

• 表的主键、外键必须有索引；
• 经常与其他表进行连接的表，在连接字段上应该建立索引；
• 经常出现在where子句中的字段，特别是大表的字段，应该建立索引
• 索引应该建在选择性高的字段上；
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 为经常出现在关键字order by、group by、distinct后面的字段，建立索引；
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
• 频繁进行数据操作的表，不要建立太多的索引，因为索引需要维护；
• 删除无用的索引，避免对执行计划造成负面影响；
• 对于那些在查询中很少使用或者参考的列不应该创建索引。既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
• 不要在有大量相同取值的字段上，建立索引。这是因为，由于这些列的取值很少，例如性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度；
• 当修改性能需求远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能需求远远大于检索性能时，不应该创建索引。
• 对字符串列进行索引，应该指定一个前缀长度（前缀索引）。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是唯一的（区分度大），那么就不要对整个列进行索引。前缀索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

（2）索引失效情况

• 使用联合索引时，要遵守最左前缀法则，就是查询的时候要有联合索引的最左列（联合索引可以避免回表查询）。
• 减少使用范围查询，因为范围查询右侧的列不能使用索引。
• 不要在索引列上进行运算，因为这样会导致索引失效。
• 字符串不加单引号也会导致索引失效，因为sql优化器进行了隐式转换。
• 尽量使用索引覆盖，避免使用select *（可以避免回表查询）