如何设计半结构化数据表？

Question

我有下面的记录，我正试着把它存储在桌子上。

{
  "properties": {
    "visitor_id": "0184d099_n00mPLKQT"
 },
  "flags": {
    "5001": true,
    "5003": true
 },
  "audiences": [
    "main_public"
 ],
  "badges": [
    "90",
    "70"
 ]
}

我已经提出了一个基本的表模式，它可以存储记录，但是插入过程需要一些时间。

 ____________________ _______ _______ _____________
| visitor_id         | badge | flags |   audience  |
| ------------------ | ----- | ----- | ----------- |
| 0184d099_n00mPLKQT |   70  |  5001 | main_public |
| 0184d099_n00mPLKQT |   70  |  5003 | main_public |
| 0184d099_n00mPLKQT |   90  |  5001 | main_public |
| 0184d099_n00mPLKQT |   90  |  5003 | main_public |
|------------------- | ------| ----- | ----------- |

我想的另一个解决方案是关于一个具有多个列的表，即为每个徽章、标志和观众设置一列。这个解决方案的好处是，我将有一个记录与访问者的所有信息，而不是多行。缺点是，如果不包含新的标志、徽章或观众，我将不得不更改表模式。

 ____________________ __________ _________ __________________ ___________ ___________
| visitor_id         | badge_70 | badge_90|   audience_main  | flag_5001 | flag_5003 |
| ------------------ | ---------| ------- | ---------------- | --------- | --------- |
| 0184d099_n00mPLKQT |    True  |  True   |     True         |    True   |   True    |
| 0100d099_n11mAABTT |    False |  True   |     True         |    True   |   False   |
|------------------- | ---------| ------- | ---------------- | --------- | --------- |

我期待着阅读大家关于在表中存储这类json记录的最佳解决方案的评论。

Answer 1

如果您可以添加更多关于数据含义的详细信息，就可以更容易地评论如何最好地构造数据。

但是通常，为所涉及的每个对象创建一个表是很好的做法。我在你的数据中至少看到了3个，也许4个对象：

1. Visitors
2. Flags
3. Audiences
4. Badges

为什么这些对象应该是单独的对象，其中一个线索是基于它们的关系基数。例如，一个Visitor可以有多个Badges，这称为一对多的关系.但是，如果同一个Badge可以给出多个Visitors，那么它实际上是多到多的基数。具有一对多关系的对象很少能够正确地在单个表中表示，具有多到多关系的对象永远不能正确地在单个表中表示。

让我们假设在您的用例中，相同的Badges、Flags和Audiences可以与多个不同的Visitors相关，从而使它们都具有多到多的关系。在本例中，您需要为每个实体的唯一列表定义一个表，并为每个实体定义一个链接/桥接表，该表将保存实体分配给特定Visitor的实例。例如，下表结构：

1. 访客
   • Visitor_Id (主键)
   • 特定于访问者的其他属性

2. 标志
   • Flag_Id (主键)
   • Flag_Value
   • 国旗特有的其他属性

3. VisitorFlags
   • Visitor_Id (复合主键和外键给访问者)
   • Flag_Id (复合主键和标志外键)

4. 观众
   • Audience_Id (主键)
   • Audience_Description
   • 特定于受众的其他属性

5. VisitorAudiences
   • Visitor_Id (复合主键和外键给访问者)
   • Audience_Id (面向观众的复合主键和外键)

6. 徽章
   • Badge_Id (主键)
   • Badge_Value

7. VisitorBadges
   • Visitor_Id (复合主键和外键给访问者)
   • Badge_Id (合成主键和徽章外键)

上面的表帮助您的数据正常化，并强制执行适当的约束，例如防止将相同的Badge多次分配给同一个Visitor。这减少了数据冗余，提高了数据的可靠性和准确性，而且从性能的角度来看通常也是最好的。

Answer 2

第一种也是显而易见的方法:标准化总是有效的。您可能更倾向于尽可能地将数据规范化；在这种情况下，至少需要4个表来存储。

1. 访客名单
2. 徽章的定义
3. 标志的定义
4. 受众的定义

对于这些表之间的每一个关系，都必须有“多到多”的表。所以你需要；

1. 访客徽章关系
2. 访客标志关系
3. 访客受众关系

表格

第二种方法:一些概括。基于aim，可以将某些数据组存储在广义表中。尽管我严格建议避免定义表(袋、旗)的泛化，但泛化关系可能是个好选择(取决于条件)。

在您的情况下，您可能更喜欢为visior badges+访问者flags+visitor受众+访问者喜欢+等创建一个泛型关系表，类似于；

 visitor_id  rel_type  rel_id
 --------    --------  --------
   1          badge       20 
   1          badge        5 
   1          flag        15  
   1          audience     5
  ....