如何为主键(col1)和json数据类型列维护多个分支

Question

我有一个设计问题，我知道JSON是最好的选择，但仍然面临实现问题。

要求:

我需要存储业务实体信息。属性有(registration_NO(主键)、Address、电话号码(多个电话号码)、董事(多个人员)等。这是由于registration_number和(电话号码和董事)之间的一对多关系，我决定使用jsonb数据类型。

有两个条件。

1. 电话号码，董事可增减等
2. 对记录的任何新更改都需要批准。在被批准之前，将显示旧值。

我面临的问题:比如说:当前记录:Regno: directors, record_status(故意不显示电话号码)

100: {John, Joe}, current; ok to display

当我添加一个新董事时，该条目必须是注册的新分支#主键，状态为"尚未批准";因此不应该出现在查询中。

100: {john, joe, jane}, updated; not yet approved.

但是，当我更新行时，董事的旧值将被覆盖。当我插入状态为"update;未批准"，然后它插入注册的重复记录#。

理想情况下，我希望有2个分支注册# 100;一个获准展出，一个不获准展出。

我该如何处理这个?如有任何帮助，不胜感激。

Answer 1

经验法则:如果您的数据具有可预测的结构，您可能不希望使用JSON列。

我将用三个表来实现它。一个表用于业务实体，一个表用于电话号码，另一个表用于董事。像这样:

CREATE TABLE business_entity (
registration_no uuid, 
address text, 
PRIMARY KEY(registration_no)
);
CREATE TYPE status AS ENUM ('added', 'deleted');
CREATE TABLE business_entity_phone_number (
registration_no uuid, 
phone_number text, 
status status, 
CONSTRAINT fk_business_entity_phone_number 
FOREIGN KEY(registration_no) REFERENCES business_entity(registration_no)
);
CREATE TABLE business_entity_director (
registration_no uuid, 
director text, 
status status, 
CONSTRAINT fk_business_entity_director 
FOREIGN KEY(registration_no) REFERENCES business_entity(registration_no)
);

状态列是用来跟踪变更的。因此，如果添加了一个新的电话号码，我们将该行标记为"已添加"。在它被批准之前，如果一行被删除了，我们会保留它，但将其标记为& deleted&;直到被批准。

然后，为了查询业务实体，我们只需要省略"added"行:

SELECT 
be.registration_no, 
address, 
(
SELECT ARRAY_AGG(phone_number) 
FROM business_entity_phone_number bepn
WHERE be.registration_no = bepn.registration_no 
AND COALESCE(bepn.status, 'deleted') != 'added'
) as phone_numbers, 
(
SELECT ARRAY_AGG(director) 
FROM business_entity_director bed
WHERE be.registration_no = bed.registration_no 
AND COALESCE(bed.status, 'deleted') != 'added'
) as directors
FROM business_entity be

这个解决方案不能完全处理的事情:

• 跟踪business_entity中其他字段的变化(如果需要的话，从问题中不清楚)
• 更新电话号码/董事(或添加/删除足够吗?)
• 当两个用户试图进行冲突的更改时会发生什么?