实现标签的方法 - 各有利弊 [英] Ways to implement tags - pros and cons of each
问题描述
以 SO 为例,如果您预计标签会经常更改,那么管理标签的最明智的方法是什么?
方式 1:严重非规范化(逗号分隔)
<前>表 帖子+--------+-----------------+|postId |标签 |+--------+-----------------+|1 |C++,搜索,代码|这里的标签是逗号分隔的.
优点:使用单个 select
查询立即检索标签.更新标签很简单.易于更新且成本低廉.
缺点:标签检索需要额外解析,难以统计有多少帖子使用了哪些标签.
(或者,如果仅限于 5 个标签)
<前>表 帖子+--------+-------+-------+-------+-------+-------+|postId |标签_1 |标签_2 |标签_3 |标签_4 |标签_5 |+--------+-------+-------+-------+-------+-------+|1 |c++ |搜索|代码 |||方式 2:稍微标准化"(单独的表,没有交集)
<前>桌柱+--------+-------------------+|postId |标题 |+--------+-------------------+|1 |你如何标记?|餐桌标签+--------+---------+|postId |标签名称 |+--------+---------+|1 |C++ ||1 |搜索 |优点:易于查看标签计数(count(*) from taggings where tagName='C++'
).
缺点:tagName 可能会重复很多次.
方式 3:酷孩子(用交集表标准化)
<前>表 帖子+--------+----------------------------------------+|postId |标题 |+--------+----------------------------------------+|1 |为什么乌鸦像写字台?|表格标签+--------+---------+|标签 ID |标签名称 |+--------+---------+|1 |C++ ||2 |搜索 ||3 |傻瓜|表格标签+--------+---------+|postId |标签 ID |+--------+---------+|1 |1 ||1 |2 ||1 |3 |优点:
- 没有重复的标签名称.
- 更多的女孩会喜欢你.
缺点:与#1 方式相比,更改标签的成本更高.
这些解决方案被称为mysqlicious
、scuttle
和toxi
.>
本文比较了每种方法的优缺点.
Using SO as an example, what is the most sensible way to manage tags if you anticipate they will change often?
Way 1: Seriously denormalized (comma delimited)
table posts +--------+-----------------+ | postId | tags | +--------+-----------------+ | 1 | c++,search,code |
Here tags are comma delimited.
Pros: Tags are retrieved at once with a single select
query. Updating tags is simple. Easy and cheap to update.
Cons: Extra parsing on tag retrieval, difficult to count how many posts use which tags.
(alternatively, if limited to something like 5 tags)
table posts +--------+-------+-------+-------+-------+-------+ | postId | tag_1 | tag_2 | tag_3 | tag_4 | tag_5 | +--------+-------+-------+-------+-------+-------+ | 1 | c++ |search | code | | |
Way 2: "Slightly normalized" (separate table, no intersection)
table posts +--------+-------------------+ | postId | title | +--------+-------------------+ | 1 | How do u tag? | table taggings +--------+---------+ | postId | tagName | +--------+---------+ | 1 | C++ | | 1 | search |
Pros: Easy to see tag counts (count(*) from taggings where tagName='C++'
).
Cons: tagName will likely be repeated many, many times.
Way 3: The cool kid's (normalized with intersection table)
table posts +--------+---------------------------------------+ | postId | title | +--------+---------------------------------------+ | 1 | Why is a raven like a writing desk? | table tags +--------+---------+ | tagId | tagName | +--------+---------+ | 1 | C++ | | 2 | search | | 3 | foofle | table taggings +--------+---------+ | postId | tagId | +--------+---------+ | 1 | 1 | | 1 | 2 | | 1 | 3 |
Pros:
- No repeating tag names.
- More girls will like you.
Cons: More expensive to change tags than way #1.
These solutions are called mysqlicious
, scuttle
and toxi
.
This article compares benefits and drawbacks of each.
这篇关于实现标签的方法 - 各有利弊的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!