等考三级数据库设计经验谈2:设计表和字段

【前言】:一个成功的管理系统由【50%业务+50%软件】组成，而50%成功的软件由【25%数据库+25%程序】组成。数据库设计的质量是一个关键。如果把企业的数据比作生命所必需的血液，那么数据库的设计就是应用程序中最重要的部分。关于数据库设计的资料很多，大学学位课程也有专门的讲座。然而，正如我们一再强调的，没有老师比经验更好。于是我总结了自己这些年走的弯路和经验，在网上找了一些在数据库设计方面颇有造诣的专业人士，教你一些数据库设计的技巧和经验。从这些技巧中挑选了60个，汇编成这篇文章。为方便索引，其内容分为五个部分:

第一部分介绍了设计一个数据库之前的12个基本技能，包括命名规范和定义业务需求(数据库设计经验[1])。第二部分介绍了设计数据库表的24个指导性技巧，涵盖了表中字段的设计以及应该避免的常见问题。
第2部分-设计表和字段
检查更改
我会在设计数据库时考虑哪些数据字段将来可能会更改。比如姓氏就是这种情况(注意是西方姓氏，比如女性婚后随夫姓等。).因此，在设置存储客户信息的系统时，我倾向于将姓氏字段存储在单独的数据表中，并且还附加开始日期和结束日期等字段，以便我可以跟踪此数据条目的变化。
使用有意义的字段名
有一次，我参与了一个项目的开发，其中包含了从其他程序员那里继承来的程序。那个程序员喜欢用在屏幕上显示表示术语的数据来给字段命名，这还不错，但遗憾的是，她还喜欢用一些奇怪的命名方式，采用了匈牙利命名和控制序列号的组合，比如cbo1，txt2，TXT2 _ B.
除非你用的是只面对你的缩写字段名的系统，请尽可能清晰地描述字段。当然也不要过分，比如customer _ shipping _ address _ street _ line _ 1。虽然很说明问题，但是没人愿意打这么长的名字，具体尺度在你手里。
按前缀命名
如果多个表中有许多相同类型的字段(如firstname)，您不妨使用特定表的前缀(如cuslastname)来帮助您识别字段。
及时性数据应包括“最后更新日期/时间”字段。时间标记对于查找数据问题的原因、按日期重新处理/重新加载数据以及清除旧数据特别有用。
标准化和数据驱动
数据标准化不仅方便我们自己，也方便别人。例如，如果您的用户界面想要访问外部数据源(文件、xml文档、其他数据库等。)，你不妨把对应的连接和路径信息存储在用户界面支持表中。此外，如果用户界面执行工作流(发送邮件、打印信头、修改记录状态等)等任务。)，那么生成工作流的数据也可以存储在数据库中。预先安排总是需要努力的，但是如果这些过程是数据驱动的而不是硬编码的，那么政策的改变和维护就会方便得多。事实上，如果流程是数据驱动的，您可以将相当大的责任放在用户身上，他们将维护自己的工作流程。
标准化不应过度
对于不熟悉标准化这个词的人来说，标准化可以确保表中所有的字段都是最基本的元素，这种措施有助于消除数据库中的数据冗余。标准化有几种形式，但第三范式(3nf)通常被认为是性能、可伸缩性和数据完整性方面的平衡。简单来说，3nf规定:
*表中的每个值只能表示一次。
*表中每一行应该具有的ID(带键)。
*依赖于其他键的非键信息不应存储在表中。
符合3nf标准的数据库有以下特点:有一组专门存储相关数据的表，用键连接。例如，存储客户及其相关订单的3nf数据库可能有两个表:customer和order。order表不包含与订单相关联的客户的任何信息，但是表中将存储一个键值，该键值指向customer表中包含客户信息的行。
标准化程度也更高，但标准化程度越高就一定更好吗？答案是不一定。事实上，对于一些项目，即使是3nf也可能给数据库带来太多的复杂性。
为了提高效率，有时不需要标准化表格。这样的例子很多。以前有一个开发餐饮分析软件的工作，就是用非标准化的表格把查询时间从平均40秒减少到两秒左右。虽然我不得不这么做，但我绝不会把数据表的非标准化当成一种自然而然的设计理念。而具体操作只是一个推导。所以，如果表出了问题，完全可以重新生成非标准化的表。
microsoft visual foxpro报表技巧
如果使用的是microsoft visual foxpro，可以使用用户友好的字段名，而不是编号名称:例如，用客户名称代替txtcnam。这样，当你用向导【向导，台湾省人管他们叫‘精灵’】创建表单和报表时，他们的名字会让非程序员的人更容易读懂。
无效或未使用的指示器
添加一个字段来指示记录在业务中是否不再有效是很有用的。无论是客户、员工还是其他人，这都可以帮助您在重新运行查询时筛选活动或非活动状态。同时也消除了新用户在采用数据时面临的一些问题。比如有些记录他们可能不再使用，删除了可以起到预防作用。使用角色实体来定义属于某个类别的列[字段]
。当需要定义属于特定类别或具有特定角色的事物时，可以使用角色实体来创建特定的时间关系，从而实现自文档化。
这里的意思不是让person实体有title字段，而是说，为什么不用person实体和person_type实体来描述人呢？例如，当工程师john smith晋升到主管john smith的职位，并最终晋升到CIO John Smith的职位时，您所要做的就是更改两个表person和person_type之间关系的键值，并添加一个日期/时间字段以了解更改发生的时间。这样，person_type表就包含了所有可能的人员类型，比如助理、工程师、主管、cio或ceo。
另一种替代方法是更改人员记录以反映新职位的变化，但这样就无法跟踪个人职位的具体时间。
使用通用实体命名组织数据
组织数据最简单的方法是使用通用名称，如人员、组织、地址和电话等。当你将这些常见的通用名称组合起来，或者创建特定的对应子实体时，你就获得了自己的特殊版本。一开始就使用通用术语的主要原因是所有具体的用户都可以将抽象的事物具体化。
有了这些抽象表示，您可以在二级ID中使用自己的特殊名称。例如，人可以是雇员、配偶、病人、客户、顾客、卖主或教师等。同样，组织可能是我的公司、我的部门、竞争对手、医院、仓库、政府等。最后，地址可以是具体的地点、位置、家庭、工作、客户、供应商、公司、现场办公室等。
使用一般的抽象术语来标识“事物”的类别，可以使您在关联数据以满足业务需求方面具有很大的灵活性，同时还可以显著减少数据存储所需的冗余。
用户来自世界各地
在设计使用网络或具有其他国际特征的数据库时，一定要记住大多数国家都有不同的字段格式，如邮政编码，而有些国家(如新西兰)没有邮政编码。
数据复制需要单独的数据表
如果您发现自己重复输入数据，请创建新的表和新的关系。
3每个表中应该添加的有用字段
* drecordcreationdate，在vb中默认为now()、在sql server中默认为getdate()
* srecordcreator，在sql server下，默认为not null默认用户
* nrecordversion，记录的版本标记；它有助于准确解释记录中出现空数据或缺失数据的原因
。使用多个地址和电话字段
仅用一行记录来描述街道地址是不够的。Address_line1、address_line2和address_line3可以提供更大的灵活性。此外，电话号码和电子邮件地址有自己的数据表，有自己的类型和标签类别。
小心过度标准化，这可能会导致性能问题。虽然地址和电话列表的分离通常可以实现状态，但如果需要经常访问这类信息，那么将“首选”信息(如客户)存储在其父列表中可能更合适。非标准化和加速访问之间的折衷是有意义的。
使用多个姓名字段
我很惊讶许多人会在数据库中留下一个姓名字段。我觉得只有刚开始这么做的开发者，但其实这种做法在网上很常见。我建议把姓和名当做两个字段，然后在查询的时候合并。
我最常用的是在同一个表中创建一个计算列[field]，通过它可以自动连接标准化的字段，这样当数据发生变化时，它也随之变化。但是，在使用建模软件时，你必须聪明。总之，连接字段可以有效地将用户应用程序与开发人员界面隔离开来。
当心大小写混合的对象名和特殊字符
过去最让我恼火的一件事就是数据库中存在大小写混合的对象名，比如customerdata。这个问题存在于对oracle数据库的访问中。我不喜欢这种混合大小写命名对象的方法，因此，我必须手动更改名称。想想看，这种数据库/应用能混到采用更强大的数据库的那一天吗？全部大写并包含下划线字符的名称可读性更好(customer_data)。不要在对象名称的字符之间留有空空格。
小心保留字
确保您的字段名不与保留字、数据库系统或常用访问方法冲突。例如，在我最近编写的odbc连接程序中有一个表，其中desc被用作描述字段名称。后果可想而知！Desc是降序缩写后的保留字。表中的一个select *语句是可以用的，但是我得到的是很多无用的信息。
保持字段名和类型的一致性
在为字段命名和指定数据类型时，一定要保持一致性。如果一个表中的字段名为“agreement_number”，则不要在另一个表中将名称更改为“ref1”。如果一个表中的数据类型是整数，那么它在另一个表中不应该变成字符类型。请记住，在您完成工作后，其他人仍然需要使用您的数据库。
仔细选择号码类型。在sql中使用smallint和tinyint类型时要特别小心。例如，如果您想查看每月的总销售额，而您的合计字段类型是smallint，那么如果总额超过32，767美元，您就不能进行计算。
删除标记
表中包含一个“删除标记”字段，以便可以将行标记为删除。不要删除关系数据库中的任何一行；采用数据清理程序，并小心维护索引的完整性。
避免使用触发器
触发器的功能通常可以通过其他方式实现。调试程序时，触发器可能会成为干扰。如果你真的需要使用触发器，你应该专注于记录它们。
包含版本机制
建议您在数据库中引入版本控制机制，以确定正在使用的数据库的版本。无论如何你必须满足这个要求。随着时间的推移，用户的需求总是会发生变化。最终，可能需要修改数据库结构。虽然可以通过检查新的字段或索引来确定数据库结构的版本，但是我发现直接将版本信息存储在数据库中更方便？
为文本字段留出足够的边距
id类型的文本字段，如客户ID或订单号等。，应该设置得比平时大一些，因为短时间内添加多余的字符很可能会让你尴尬。例如，假设您的客户id是10位数。然后，您应该将数据库表字段的长度设置为12或13个字符。这是不是浪费空房间？有点，但没你想的那么多:如果一个字段加长3个字符，100万条记录，一点点索引，只会让整个数据库占用3mb的空。但是，这个额外的空空间可以增加数据库的大小，而无需在将来重建整个数据库。身份证号从15位变成18位是最痛苦的例子。
列[字段]命名技巧
我们发现，如果对每个表的列[字段]名称使用统一的前缀，那么在编写sql表达式时会大大简化。它确实有缺点，例如，它破坏了自动表连接工具的功能，该工具将公共列[字段]名称与一些数据库连接起来，但即使是这些工具有时也连接不正确。举个简单的例子，假设有两个表:
customer和order。客户表的前缀是cu_，所以表中的子段名称如下:cu_name_id、cu_surname、cu_initials、cu_address等。订单表的前缀是or_，所以子节名是:or_order_id，or_cust_name_id，or_quantity，or_description等。
从数据库中选择所有数据的sql语句可以写成这样:
Select * from customer，order where cu _ surname = " myname
and Cu _ name _ id = or _ cust _ name _ id and or _ quantity = 1
如果没有这些前缀，就这样写(用别名区分):
select * from customer，order where customer . surname = " my name "；
and customer . name _ id = order . cust _ name _ id and order . quantity = 1
第一条sql语句没有丢失多少字符。但是如果查询涉及五个表甚至更多的列[字段]，就知道这个技术有多有用了。
预览:第三部分——选键
如何选键？这里有10个技巧，具体涉及正确使用系统生成的主键，以及何时以及如何索引字段以提高性能等。