Json and relational tables are descriptions of different relationships.

json:

Take marketing campaigns and marketing rules for example. If a marketing rule is a JSON field of a marketing campaign, then the marketing rule is not an entity; it is an attribute of the marketing campaign. Further, if I customize 300-20 marketing rules for the May Day promotion, the rules become an attribute of the May Day Promotion. As the name of the marketing campaign is May Day Promotion, the rule is not a separate entity at this time, its existence depends on the existence of the May Day Promotion marketing campaign. When the National Day campaign goes online, I need to describe the marketing rules of the campaign again for the National Day Campaign, even if it is still the same rules for the full 300-20.

Relational tables:

If a marketing campaign is an entity, so are marketing rules. If I create a 300-20 marketing rule for May Day, this marketing rule is an entity. There is a relationship between it and marketing activities that rules are used by activities, but it is not an attribute of activities, and its existence does not depend on the existence of May Day marketing activities. When the National Day campaign goes online, I just need to associate marketing rules for the National Day campaign. To describe the relationship between the two entities at this point, you need to use a relational table. And because a marketing rule can exist in more than one marketing campaign, a marketing campaign can also use more than one marketing rule, is a many-to-many relationship. Is a promotion_RULE_REL relational table.

Json fields and relational tables describe different relationships between entities. If you want rules to be reusable, you can use relational tables, but at the cost of maintaining one more table. The corresponding query overhead also increases.

Json and relational tables have their pros and cons.

json:

The same goes for marketing campaigns and marketing rules. The advantage of JSON is that the data structure is flexible. If the May Day promotion my rules are full 300-20 [full reduction], buy enough 2 send 1 [full gift], 20% off [discount], in short, there are many kinds of marketing rules. If I use database tables to describe variable rules, because database tables have relatively fixed fields, we need to describe variable rules with either a large number of redundant fields or frequent field changes. Poor flexibility and extensibility. Instead, if I use JSON. I just have to construct different value objects like

{"goodsCount": 2, ## "giftCode": Ladder: 100, ## ladder" discount: 20 ## discount}}Copy the code

Obviously, flexibility and extensibility are better.

But JSON also has its drawbacks. Although the key value in the JSON field can be obtained through the DATABASE JSON function, the search efficiency is lower than that of the table field. Imagine a scenario where a marketing campaign has a JSON field for a marketing product, as in


{"productList": [{"courseId": 463, "weekCount": 1}, {"courseId": 462, "weekCount": 48}]}

Copy the code

If I want to know which marketing activities of the courseID =463 course have been used, this case of reverse lookup of database records with the key value in the JSON field will produce complex SQL. Can increase the query overhead. Furthermore, use json fields with caution if a key contains a foreign key or information that is read and written frequently. And from the perspective of the relational database paradigm, the JSON field itself should not conform to the database First paradigm, and an attribute in an entity can be split into multiple values. If we decide to use a JSON field, we should consider the condition that 1) the data query flows to: the JSON field of the database row -> row. That is to obtain a database record, according to this record to obtain the information in JSON. There is little or no reverse lookup path, the JSON field of the row -> database row. 2) You need the flexibility that json structures bring

Relational tables:

The advantage of relational tables is the key relationships between all entities associated in a database row. Query efficiency is very good, two-way query is very convenient. The disadvantage of relational tables is that there is one more table to maintain. If all relationships are linked by relational tables, many join operations will definitely occur. In the long run, the expansion of the number of tables increases the maintenance cost and is not conducive to the stability of the system, and the join operation costs performance. But if your system is well built with domains, order domains, goods domains, and databases controlled from the granularity of service modules, table bloat is not a problem.