mysql - 数据表关联字段命名问题

Question

这是一张文章表，后面关联了node表，和category表。分别关联对应表的id字段。

节点表：

分类表：

接下来就有一个问题：

1. 如果文章表按上面的设计，当我查询某篇文章的所属节点名称和所属分类名称。需要关联查询节点表和分类表。

2. 我想把文章表的node_id 和 category_id 分别设置为 node_name 和 category_name,分别与另外两张表相关字段对应。查询的时候就可以直接取出，而不用关联其他表。这样是否更加合理？

Answer 1

That’s not the case.
Suppose your article table is called the article table, where node_id and category_id are foreign keys, pointing to the node table and category table respectively.

Replace node_id with node_name and category_id with category_name as you said. Please think about a few questions:

1. If node_name or category_name needs to be updated in the future, will this field of the article table be updated?

2. And if you need to query the number of clicks on the nodes corresponding to articles that meet certain query conditions in the future, is it more efficient to query through node_id or node_name?

3. If the fields of the node table and the category table need to be expanded in the future, which one is better, the existing table structure or the modified table structure?

In fact, what worries you is that when querying the article table, you need to join the node table and the category table query. At this time, you can consider whether the article table needs redundancy of the node table node_name and the category fields of the category_name table. Although redundant fields will destroy the third normal form, appropriate redundant fields can improve query efficiency. This requires a business balance.
Moreover, redundant fields also face the problem of how to maintain data consistency. For example, if the node field of the node_name table is updated, the article of the node_name table must also be updated.

Or you can also use views to solve this problem, and the method is more flexible.

CREATE OR REPLACE ALGORITHM=MERGE VIEW `v_article` AS (
    SELECT a.id, a.title, ..., b.node_name, c.category_name FROM article AS a,
    JOIN node AS b ON a.node_id = b.node_id
    JOIN category AS c ON a.category_id = c.category_id
    WHERE ... #如有需要，这里可以加上一些过滤条件
);

However, because the view uses the join table, sometimes the query efficiency may not be high. This requires more attention from the business and use EXPLAIN to analyze SQL.

Answer 2

You can design a custom tag, and the page will directly convert the id into a value. Of course, you can actually check it in the background