How to design a table for semi-structured data?

Question

I have the following record and I am trying to store it in a table.

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

I have come up with a basic table schema that can store the record but the insertion process takes some time.

 ____________________ _______ _______ _____________
| 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 |
|------------------- | ------| ----- | ----------- |

Another solution I thought was about a table with multiple columns, that is, a column for each badge, flags and audiences. The benefit of this solution is that I will have 1 record with all the information of the visitor instead of multiple rows. The downside is that I will have to change the table schema in case a new flag, badge or audience is not contained.

 ____________________ __________ _________ __________________ ___________ ___________
| 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   |
|------------------- | ---------| ------- | ---------------- | --------- | --------- |

I am looking forward to reading all of you comments about what's the best solution to store this type of json record in a table.

Answer 1

If you could add more details on the meaning of your data, it may make it easier to comment on how to best structure it.

But generally, it's good practice to create a table for each object involved. I see at least 3, maybe 4 objects in your data:

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

One clue into why these should be separate objects is based on their relational cardinality. For example, one Visitor can have multiple Badges, this is called a one-to-many relationship. But if the same Badge can be given to multiple Visitors it's actually a cardinality of many-to-many. Objects with one-to-many relationships rarely can be expressed in a single table properly, and objects with many-to-many relationships can never be represented in a single table properly.

Let's assume in your use cases, the same Badges, Flags, and Audiences can be related to multiple different Visitors, thus making them all many-to-many relationships. In this case, you'd need to define a table for the unique list of each entity, and a linking / bridge table for each entity that'll hold the assigned instance of the entity to a specific Visitor. For example the following table structures:

1. Visitors
   • Visitor_Id (primary key)
   • Other properties specific to a Visitor
2. Flags
   • Flag_Id (primary key)
   • Flag_Value
   • Other properties specific to a Flag
3. VisitorFlags
   • Visitor_Id (composite primary key, and foreign key to Visitors)
   • Flag_Id (composite primary key, and foreign key to Flags)
4. Audiences
   • Audience_Id (primary key)
   • Audience_Description
   • Other properties specific to an Audience
5. VisitorAudiences
   • Visitor_Id (composite primary key, and foreign key to Visitors)
   • Audience_Id (composite primary key, and foreign key to Audiences)
6. Badges
   • Badge_Id (primary key)
   • Badge_Value
7. VisitorBadges
   • Visitor_Id (composite primary key, and foreign key to Visitors)
   • Badge_Id (composite primary key, and foreign key to Badges)

The above tables help normalize your data, and also enforce proper constraints, such as preventing the same Badge from being assigned to the same Visitor more than once. This reduces data redundancy, improves data reliability and accuracy, and generally is best from a performance perspective too.

Answer 2

First and obvious approach: Normalization always works. You may prefer to normalize data as much as possible ; In that case you need at least 4 table to store

1. List of visitors
2. Definition of badges
3. Definition of flags
4. Definition of audience

And there must be "many to many" tables for every relation between these tables. So you would need;

1. visitor badges relation
2. visitor flags relation
3. visitor audience relation

tables

Second approach: Some generalizations. Based on aim, some group of data may be stored within generalized tables. Although, i strictly advise to avoid generalization of definition tables ( bagdes, flags, ), generalization of relations may be good choice (depends on conditions)

In your case you may prefer to create a generic relation table for visior badges+ visitor flags+visitor audiences + visitor likes + etc something like;

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