无痛 SQL Schema 的10 条军规

Question

在新建表和数据仓库时，往往需要做出许多决定。一些在当时看起来似乎是无关紧要的决定，却最终会导致你和你的客户在使用数据库的整个过程中饱尝痛苦。

我们已经和数千人以及他们的数据库打过交道了，在经历了无数个小时的读写查询之后，可以说我们几乎见过所有的情况了。下面是我们总结出的有助于创建无痛模式（Schema）的 10 条规则。

1. 只使用小写字母，数字和下划线

不要在数据库、模式、表格或者列名上使用点、空格或者破折号。因为点号是用来识别对象的，通常只在 database.schema.table.column 这种情况下使用。

在对象的名字中也包含点号会带来困扰。同样地，在对象名中使用空格将迫使你给查询加入一堆不必要的引号：

select "user name" from events

-- vs

select user_name from events

另外，一旦在表格或者列名上使用了大写字母，查询就会变得更加难写。因为如果全部都是小写字母的话，人们就没有必要去特别记忆用户表到底是 Users 还是 users 。

不仅如此，当你最终变更数据库或者将表复制到数据仓库中时，除了一些数据库以外，你无须记住哪个数据库是大小写敏感的。

2. 使用简单且具有描述性的列名

如果 users 表需要定义一个引用了 packages 表的外键，那么将其命名为 package_id 是个不错的选择。我们应该避免像是 pkg_fk 这样的又短又含糊的列名，因为其他人很难知道那是什么意思。具有描述性的名字能够使得其他人更容易理解模式，而且当团队扩大时这一点对于保持工作效率也是很重要的。

不要使用模棱两可的名字命名可能有多种解释方法的数据。如果你发现自己正在以 item_type 或 item_value 这样的命名风格创建列时，那么可能就说明你应该使用更多的带有具体名字的列了，像是 photo_count 、 view_count 和 transaction_price 。

因为这样做的话，列中存储了什么样的数据总是可以由模式得知的，而并不需要由行中的其他值推导出来。

select sum(item_value) as photo_count
from items
where item_type = 'Photo Count'

-- vs

select sum(photo_count) from items

不要使用表名作为列名的前缀。因为一般来说在 users 表中定义诸如 user_birthday 、 user_created_at 或者 user_name 这样的列名起不到什么辅助作用。

最后，还要避免将诸如 column 、 tag 或 user 这样的保留关键字用作列名。因为一旦使用了保留关键字，就意味着不得不在查询语句中使用额外的引用符，而当有人忘记这么做的时候，数据库就会产生非常令人困惑的错误信息。而且如果在本该是列名出现地方使用了关键字，那么数据库就无法理解查询语句。

3. 使用简单且具有描述性的表名

如果表名是由多个单词组成的，那么请使用下划线分割它们。因为 package_deliveries 要比 packagedeliveries 更 容易读。

如果可能的话，总是使用一个单词而不是两个，因为 deliveries 要更加容易阅读。

select * from packagedeliveries

-- vs

select * from deliveries

不要用模式的名字作为表名的前缀。如果你需要将一些表划入一个范围，那么只需将这些表放入到一个模式中即可。和有前缀的列名一样，诸如 store_items 、 store_transactions 或者 store_coupons 这样的表名，通常都是不需要额外的前缀的。

我们推荐使用复数形式的名字为表命名（例如 packages），并且对联合表的表名中的两个单词也都使用复数形式。单数形式的表名更可能意外地与关键字冲突并且一般在查询中其可读性也不高。

4. 将整形作为主键

无论你是正在使用各种 UUID（通用唯一识别码）类型的列作为主键，还是你认为加入带有自增长整型序列的主键根本没有意义（比如对于联合表），我们都建议你添加一个带有自增长整型序列的标准 id 列。这种类型的主键会使得特定的分析变得更加容易，比如从一组数据中只选出第一行。

并且当导入数据的工作导致了数据的重复时，主键也会成为灵丹妙药，因为我们可以通过主键轻松删除特定的行：

delete from my_table
where id in (select ...) as duplicated_ids

避免多列主键。当努力编写高效的查询时，多列主键将会导致查询语句很难理解，并且很难修改。我们可以使用一个整型的主键，或者一个多列的唯一约束，再或者一些单列的索引来取代多列主键。

5. 与外键一致

命名主键和外键有许多种风格。我们建议诸位使用的是最为普遍的风格，即对于任意的表格 foo，将 foo 中的主键命名为 id ，将所有的外键命名为 foo_id 。

另一种风格是使用全局统一的主键名。在这种风格下，表 foo 的主键称为 foo_id ，而所有的外键也称为 foo_id 。不过无论使用哪种风格，使用缩写的话（比如将 users 表缩写为 uid )，总是会造成困扰或名称冲突，所以应该避免使用缩写。

而且，无论你选用了什么风格，都要坚持下去。不要在某些地方使用 uid ，而又在其他地方使用 user_id 或者 users_fk 。

select *
from packages
  join users on users.user_id = packages.uid

-- vs

select *
from packages
  join users on users.id = packages.user_id

-- or

select *
from packages
  join users using (user_id)

除此之外还要留意外键并不显式匹配一张表的情况。一个名为 owner_id 的列可能是 users 表的一个外键，当然也可能不是。因此如果有必要的话，请将作为外键的列命名为 user_id 或者 owner_user_id 。

6. 将日期时间存储为各种日期时间类型

不要使用 Unix 的时间戳或者字符串来存储日期，而是要将它们转换为各种日期时间类型。虽然 SQL 的日期计算函数并不是最棒的，但是调用这些函数来处理时间戳总比自己来处理要简单。在查询时，我们需要为每一个涉及到从 timestamp 到 datetime 类型的转换的查询调用 SQL 的日期函数

select date(from_unixtime(created_at))
from packages

-- vs

select date(created_at)
from packages

不要将年、月、日分别存储到不同的列中。因为这样会导致每个有关时间序列的查询都更加难写，而且也会在使用这张表的日期信息时给大多数的 SQL 初学者造成障碍。

select date(created_year || '-' 
  || created_month || '-' 
  || created_day)

-- vs

select date(created_at)

7. 总是使用 UTC

使用时区而不是 UTC 将导致无穷无尽的问题。好的工具（包括我们的 Periscope）拥有你所需要的从 UTC 转换为你所在时区数据的所有功能。在 Periscope 中，简单地加个:pst 就可以将 UTC 转换为太平洋时间。

select [created_at:pst], email_address
from users

应该将数据库的时区设为 UTC，并且所有 datetime 的列都应该是剥离时区后的类型（如，无时区的 timestamp）。

如果你的数据库的时区不是 UTC，或者你的数据库混合了 UTC 和非 UTC 时间日期，那么时间序列的分析查询将会变得更加困难。

8. 单一的真相源

一块数据应该只有单一的真相源（Source of Truth）。视图和汇总（Rollup）本身应该有所标示。这样做的话，数据的消费者就会知道他们使用的数据和原生真相之间的区别。

select *
from daily_usage_rollup

另一方面，将诸如 user_id 、 user_id_old 或者 user_id_v2 的遗留列都保留的话，只会带来无尽的困扰。因此请确保在日常维护中会进行删除废弃的表格和不再使用的字段的工作。

9. 优先使用没有 JSON 列的表格

请不要使用列过多的表。如果一张表有超过几十个列并且其中一些是以序列命名（例如，answer1、answer2、answer3）的话，那么马上你就会感到不好过了。

正确的做法是将这样的表转化为不包含重复列的模式，因为这样的模式将很容易查询。例如，在一个查询中计算某个调查表中已完成的题目的数目：

select
  sum(
    (case when answer1 is not null
      then 1 else 0 end) +
    (case when answer2 is not null
      then 1 else 0 end) +
    (case when answer3 is not null
      then 1 else 0 end)
  ) as num_answers
from surveys
where id = 123

-- vs

select count(response)
from answers
where survey_id = 123

对于查询分析而言，从 JSON 列抽出数据的操作将大幅降低查询的性能。虽然有很多很棒的理由支持我们在产品中使用 JSON 列，但是对于查询分析来说并不是这样。大胆得将 JSON 列拆解为更简单数据类型，可以使查询分析变得更快更容易。

10. 不要过度规范化

日期，邮编和国家不需要使用带有外键查询的表单独存放。过度地规范化将会导致每个查询后面都要带上一些相同的表连接操作。这样不但创建了许多重复的 SQL，而且数据库为此还要做很多额外的工作。

select
  dates.d,
  count(1)
from users
  join dates on users.created_date_id = dates.id
group by 1

-- vs

select
  date(created_at),
  count(1)
from users
group by 1

表是数据库中的一等对象，拥有很多属于自己的数据。其余的任何数据都可以作为另一个更重要对象的附加列。

更好的模式在等着你！ 如你所期待的，对于你和团队的新成员来说，遵循这些规则将有助于下一张表或者数据仓库变得更容易查询。如果你不认可或者有更多的规则建议，请联系我们吧。我们很期待听到你的声音！