数据库表不确定字段解决方案
文章首发在个人博客
分析
通常来说，在需求分析阶段会确定数据库表中的字段，同时也普遍存在一种需求，那就是表中的字段除了常见的以外，还存在一些不确定的字段。譬如电商系统，有一个 products 表保存商品记录，然而各行各业的商品信息不一样，不可能因为某一行业商品特有的属性而在表中添加一个字段。面对这样的需求，如何设计数据库呢？
对策
文中提出来的解决方案的前提是系统所用的数据库支持 hash 数据类型，其中 PostgreSQL 支持 Hstore ，正好是我们需要的数据类型。所以前提是使用了 PostgreSQL 作为系统数据库在本文的讨论中才适用。
具体的实现思路细节是采用 Ruby on Rails 框架，不熟悉在 Rails 使用 psql 特性的读者可以先看看这篇文章 Active Record and PostgreSQL 。
在 Rails 的 ActiveRecord 中，使用 serialize 能够把 hash, json, array 类型的数据以 text 数据类型保存在数据库中。但是这种方式不能查询保存在数据库之前 hash json array 的原数据。ActiveRecord Base。PostgreSQL hstore 能补充这个短板。
Active Record can serialize any object in text columns using YAML. To do so, you must specify this with a call to the class method serialize. This makes it possible to store arrays, hashes, and other non-mappable objects without doing any additional work.
PostgreSQL 数据库保存以 hstore 数据类型保存的 hash 中的能够查询其 key 和 value 。但其有一个缺点，字段为 hstore 数据类型中的 key 和 value 都是字符串，开发者需要转换成需要的类型。例如业务逻辑中对 value 的要求是 integer ，那么就应该把以 hstore 数据类型的字段中的 hash 的 value 转化为 integer 。
Hstore 在 Rails 中的使用
在 Rails app 中使用 Hstore ,需要显式地开启支持 hstore 数据类型的扩展
rails g migration enable_hstore_extension
生成数据库迁移文件
class EnableHstoreExtension < ActiveRecord::Migration
def change
enable_extension 'hstore'
end
end
结合第一段提到的商品这个案例，首先有建立两个数据库表 product_descriptions 和 products 表。
# 执行命令
rails g migration CreateProductDescriptionTable info:string user:references
# 迁移文件
class CreateProductDescriptionTable < ActiveRecord::Migration[5.0]
def change
create_table :product_description_tables do |t|
t.string :info
t.references :user, foreign_key: true
end
end
end
product_descriptions 有一个字段保存商品的属性，另外和 users 表关联，以此用户就能自行设定商品所属行业的属性信息。就是常规的 products 表的字段变成 product_descriptions 表的记录。那么即使各种不同行业的有不同商品属性，通过在 product_descriptions 添加记录而不是在 products 表添加字段。另外当新建用户，根据所属行业可以在 product_descriptions 表生成该行业默认的一些商品属性记录，以此减少用户后面添加商品属性的工作量。
不用随着不同商品属性而添加字段的问题解决了，那么用户商品的记录怎么保存呢？
在 products 表中，我们仅仅有一个数据类型为 hstore 的 info 字段保存商品信息，同时和 users 表关联。Hstore 的 key 是当前用户在 product_descriptions 表中添加的记录。
# 执行命令
db/migrate/20170225131658_create_product_table.rb
# 迁移文件
class CreateProductTable < ActiveRecord::Migration[5.0]
def change
create_table :product_tables do |t|
t.hstore :info
t.references :user, foreign_key: true
end
end
end
生成 products 表记录的过程如下：利用当前商户在 product_descriptions 表的商品属性记录构建 hash 数据结构，然后赋值给 products 表的 info 字段。
Hstore 数据类型检索相关
相比 ActiveRecord 的 serialize ，使用 PostgreSQL Hstore 数据类型最大的优势就是能通过 hstore 查询。hstore 数据类型支持两种建索引的方式 GiST or GIN 选择哪种建索引方式可参考下面两点
- GIN indexes are three types faster to search, but they take more time to index. They also take more disk space. Use it when you have more than 100K unique terms.
- GiST indexes are slower than GIN indexes, but they’re faster to update. Use it when you have up to 100K unique terms.
# 执行命令
rails g migration AddIndexForInfoInProduct
# 生成迁移文件
class AddIndexForInfoInProduct < ActiveRecord::Migration[5.0]
def change
add_index :products, :info, name: 'products_info_idx', using: :gin
end
end
一些常见的查询
# find all products that with the name key
Product.where("info ? :key", key: "name")
# find all products that without the name key
Product.where("not info ? :key", key: "name")
# Find an product by name with a specific value
Product.where("info @> hstore(:key, :value)",
key: "name", value: "product_name"
).first
# Find all products that have a Name with the substring %nan%
Product.where("info -> :key LIKE :value",
key: "name", value: "%nan%"
)
推荐阅读
