请教一个内容分级可见的数据库设计的问题？

[whooami]

为了简化一下问题的描述，以下统用省、市、县、乡、村来表示可见级别。 比如说发一篇文章，文章可以设置显示区域[多个区域]。访问也可能归属于多个区域[比如即住在北京，有时也住在天津] 1 、比如可以设置兰考县， 那当访客是 兰阳街道、考城镇 都是可以见到的 2 、比如只设置考城镇， 那就只有这样一个镇的访客可见 3 、也可以设置可见为 兰考县、 设置不可见为 兰阳街道，那么这时除了 兰阳街道，兰考县下的其它城镇都可以见到。 4 、也可以单独设置多个区域，比如说只对北京、天津访客可见。也可能只对北京 11 、考城镇 410225103 这两地可见

t_city 级别（省市）表结构如下

id	名称 name	父 ID pid
10	中国	0
11	北京	10
12	天津	10
……	……	……
41	河南省	0
1101	北京市	11
4101	郑州市	41
4102	洛阳市	41
……	……	……
410202	龙亭区	4102
410204	鼓楼区	4102
410225	兰考县	4102
410225001	兰阳街道	410225
410225103	考城镇	410225

t_article 文章表

字段	中文
id	主键
title	标题
content	内容

初步设想 直接在 t_article 加字段

字段	中文
visible_city_ids	可见城市 ID 11,410225
invisible_city_ids	不可见城市 ID, 410225103

文章 1 为北京、以除考城镇之外的全部兰考县可见

visible_city_ids = 11,410225
invisible_city_ids = 410225103

文章 2 以除考城镇之外的全部兰考县可见

visible_city_ids = ''|null
invisible_city_ids = 410225103

第二种 建立中间表

t_article_to_city

id	文章 id	区域 ID	是[1]否[0]可见
1	1	11	1
2	1	410225	1
3	1	410225103	0
4	2	410225103	0

这样基本可以满足需求，但是在查询的时候要对比访客的各种级别、以及归属。比如说访客 A 本身考城镇的，还是河南这个地区的 BOSS ，就算设置了兰考县不可见，A 还是可以看见这篇文章的。

不过这样查询的 sql 会比较复杂，所以想来请教下各位 DBA 大佬，这种场景一般怎么样设计，SQL 怎么样写查询效率会比较高，还是说会放到程序里面去处理？ 抱歉，可能我说的有点乱。如果还有没说清楚的地方还请指出，谢谢

[YVAN7123]

我理解你的问题是关于如何设计一个数据库模型和编写高效的查询语句，以满足文章可见性在多个区域设置下的需求。你提到了两种设计方案，我会就每种方案分别进行讨论，并提供一些可能的解决方案。

### 方案一：在 t_article 表中加字段

这种方案中，你在文章表 t_article 中加入了两个字段，分别表示可见城市和不可见城市的 ID 列表。这样的设计在简单的情况下是可行的，但在复杂的查询和处理情况下可能会变得繁琐。

**查询的复杂性：** 当涉及多层级的区域关系以及访客的属地归属时，编写查询可能变得复杂。需要处理访客属地与文章可见性的交叉匹配。

**解决方案：** 如果冗余数据量不大，你可以尝试在程序层面进行一些数据预处理，将一些常见的查询结果缓存，以减轻数据库查询压力。

### 方案二：建立中间表 t_article_to_city

这种方案通过建立一个中间表 t_article_to_city ，记录文章和可见城市/不可见城市的关系。这种做法在处理复杂的多对多关系上更加灵活，但查询可能会涉及多表连接。

**查询的复杂性：** 查询涉及到多个表的连接，可能会增加查询的复杂性和性能开销。

**解决方案：** 使用合适的索引来优化查询性能，尽可能避免全表扫描。合理使用缓存，以及数据库优化技术，如合理的索引设计、查询优化等。

### 建议的优化方案：

1. **索引优化：** 无论哪种方案，都要确保你的表上有合适的索引。对于 t_article_to_city 表，可以在 `article_id` 和 `city_id` 列上建立复合索引，以加快查询速度。

2. **缓存：** 考虑使用缓存来缓存常用的查询结果，这可以减轻数据库的负担。例如，可以缓存某个访客在某个时间点下有权访问的文章列表。

3. **存储过程和函数：** 在数据库中，你可以使用存储过程或函数来封装复杂的查询逻辑，使得业务逻辑更集中，同时减少网络传输开销。

4. **数据库分区：** 如果数据量较大，可以考虑使用数据库分区技术，将数据按照一定规则分散存储，以提高查询性能。

5. **合理拆分数据：** 根据实际业务情况，合理拆分数据表，避免一张表的数据量过大。

6. **测试和优化：** 在设计数据库结构和编写查询语句后，一定要进行充分的测试和性能优化，确保系统在不同访问情况下都能够正常高效运行。

总的来说，数据库设计和查询性能优化是一个复杂的过程，需要根据实际情况进行权衡和调整。在设计过程中，要考虑数据规模、查询频率、数据变更频率等因素，以及合理使用数据库技术和优化手段来满足业务需求。同时，将一些复杂的业务逻辑尽可能地移到程序层面，以减轻数据库的压力。

[wxf666]

感觉直接在 t_article 加字段比较可行。

大家也常说，回表查询，速度会变慢嘛。搞个中间表，不就相当于回表查询了。。

但应该也有限度。如果设立的区域过多，还不如直接去有索引的表查了？

[whooami]

@YVAN7123 感谢回复，不过 GPT 的回答很正也很废啊
@wxf666 应该不会太多，目前的设置多级就是为了规避设置太多的，如果太多就先上一层级。但是带来的问题就是查询的时候同一个字段可能是多个层级对比了。所以请教下有没有更好的方案？

[v2eb]

1.可以查看的各个区域都新增一条相同数据,
2. 各个区域新增一条数据, 加个字段指向源数据, 便于修改

[icql]

感觉这个场景存白名单就行。中间表字段：文章 id/省 id-市 id-区 id-镇 id ，文章 1 河北省石家庄市和湖南省 可见，就存两条记录 文章 1/河北省-石家庄市-ALL-ALL 文章 1/湖南省-ALL-ALL-ALL ，查询时只有一个 poi 地址组合一下 ALL 用 in 去查。如果不加中间表要用可见性过滤分页查文章就可能不行了

[whooami]

@v2eb
1 是说第二种方案中间表的设计吗？就像 t_article_to_city 表中 id in (1,2) 每个区域对应一条记录
2 不好意思，我有点没太看懂

[whooami]

@icql 目前是白名单加黑名单，我是准备直接以 city_id 来查的，地址组合有点太占空间并且查询比对效率有点不太行

[newaccount]

看起来像是经典的树形菜单存表问题？
如果使用关系表保存，是很灵活，但是查询起来烦的要死
一般还是通过字符串直接级别拼接的方式，查询可以左 like ，方便，效率也还可以
排除列表是不是可以通过第二个查询，然后程序里做个 A - B 来处理呢

[zypy333]

jeecg 用 like ，每个业务表一个字段 sys_org_code ，上级编码 A01 ，下级编码 A01A02 类似这样

[v2eb]

@whooami 两个都没有中间表, 只在文章表里操作。1 是在文章表里添加多条记录, 区分地域。2 还是在文章表里添加多条记录, 区分地域, 由于多条数据需要考虑一致性问题, 加个字段指向初始数据。

[Mandelo]

做过类似的，有个属性叫可视范围，公开，私密，指定可见这些。私密就是筛选创建人自己如果指定范围就会弹出个带 checkbox 的树，中间表存的是根结点 id 和文章 id 。

[vivisidea]

我会倾向于中间表 t_article_to_city
article 表里面加字段 visible_city_ids 的方式，如果 id 很多呢？上百个怎么办，你的字段长度怎么定？
如果要统计哪些文章在某个城市可见呢？用 like 查询么？

[zhazi]

看下 abac 基于请求者拥有的属性授予对服务的访问权

[cpstar]

我觉得最复杂的是用关系型表来表述树状结构，也就是 t_city 这个，然后问题就出现在，如果对北京不可见，对海淀可见么？那么这里自然有一个继承，怎么继承，即便是上了 t_article_to_city ，那一个 1-11-0 是否还需要搞 1-1101-0 以及 1-110106-0 ？估计再关系表的区域 ID 上，还需要做点计算文章。

[whooami]

@Mandelo 类似吧，不过这个是 Pc 端的设计风格。移动端要简洁一点，看来你们是方案二
@vivisidea 这个 find_in_str
@zhazi abac 也算可以吧，这个也是中间表，方案二
@cpstar 这个其实涉及一个优先级的问题。我目前的想法是可见优先，并且可见层级是高于不可见层级的。可以实现，但是的确有点复杂，所以想来请教下看有没有更简单的方案。

[knightdf]

看起来就是资源权限问题

[whooami]

@knightdf 怪我需求没说清楚，这个并没有严格的要求，只是不是让你主动可见的信息。但是如果别人转发给你，你还是可以看的。

[Rever4433]

你这个需求是用户管理系统里的部门概念的升级版。考虑到行政区的数据量，我认为中间表是最合适的。

[whooami]

@Rever4433 是的，有点类似。有没有你说的用户系统中的部门概念的优良设计及实现可以参考下

[Rever4433]

@whooami 参考下若依 http://www.ruoyi.vip/

[whooami]

@Rever4433 嗯，之前看过，他这个只是最普通的权限系统

[hfywy]

黑白名单不多的话，建议是用 t_article 加字段方案，可以减少中间表的查询。黑白名单多的话，只用中间表估计也不够，请求量大的情况下建议加一层缓存。
BOSS 角色这个就是判断优先级的，角色级别已经可见，就可以不用往下判断区域可见性。