电商系统中的仓库数据结构和分配查询

这篇文章主要讲述两个问题:

如何设计仓库的数据结构(用关系型数据库的话即表结构)。
如何查询相匹配的仓库。

仓库的数据结构

表间关系图

relations

箭头所指表示所属对象。
warehouse 自身会有一个地址，所以它属于某一个 address。
address 属于某一个 state, 即某省，某直辖市等。
state 属于某一个或者多个 zone。
zone 与 zone 之间也可能有一种从属关系, 比如我们建立了长株潭(长沙，株洲，湘潭)这个 zone, 这个 zone 又可能是属于华中这个 zone。

通过上面的分析，我们建立实际的表:

warehouses 表

     Column      |            Type             |                        
-----------------+-----------------------------+---------------------------
 id              | integer                     | 主键
 name            | character varying(255)      | 仓库名
 address_id      | integer                     | 仓库自身所在地址
 zone_id         | integer                     | 关联所属区域

addresses 表

                                        Table "public.addresses"
      Column       |            Type             |                                               
-------------------+-----------------------------+-------------------------
 id                | bigint                      |  主键
 firstname         | character varying(255)      |  姓
 lastname          | character varying(255)      |  名
 address1          | character varying(255)      |  地址1 比如街道
 address2          | character varying(255)      |  地址2 比如五环外等
 city              | character varying(255)      |  城市名
 state_id          | integer                     |  关联 state

states 表

   Column   |            Type             |                                            
------------+-----------------------------+-------------------------------
 id         | integer                     | 主键
 name       | character varying(255)      | 名

zones 表

   Column    |            Type             |                                          
-------------+-----------------------------+-------------------------------
 id          | integer                     | 主键
 name        | character varying(255)      | 名

现在还剩下 states 和 zones, zones 和 zones 这两个关系还没有建立起来, 经过一些考量，我们认为这两个关系都属于 zones 的成员关系，于是我们建立 zone_members 表来表示这一关系。

zone_members 表

    Column     |            Type             |                                                  
---------------+-----------------------------+--------------------------------
 id            | integer                     | 主键
 zone_id       | integer                     | 关联 zone，即所属的 zone
 zoneable_id   | integer                     | 关联成员
 zoneable_type | character varying(255)      | 有 State 和 Zone, 也可以根据需要扩展

现在完成了仓库相关的表设计，我们进入下一步:

查询相匹配的仓库

首先假设我们有如下的一个发货地址,

-[ RECORD 1 ]-----+------------------------------
id                | 1
firstname         | 张
lastname          | 三
address1          | 某某区
address2          | 某某街道
city              | 大理
state_id          | 10002
zipcode           | 470001

那我们如何为上面的地址分配一个仓库呢? 这个问题用一条 sql 就可以解决,

select * from warehouses w join zones z on z.id = w.zone_id
join zone_members zm on zm.zone_id = z.id
join states st on st.id = zm.zoneable_id
join addresses a on a.state_id = st.id
where zm.zoneable_type = 'State' and a.id = 1

参考

spree: https://github.com/spree/spree