将数组存储为字段值或将数组值存储为记录是一个好主意吗？ [英] Is it ever a good idea to store an array as a field value, or store array values as records?

问题描述

在我的应用程序中，我获得了带有描述性预定义标签的文章（类似于帖子/推文/文章）：即困难，简单，红色，蓝色，业务等等

In my application I've got "articles" (similar to posts/tweets/articles) that are tagged with descriptive predefined tags: i.e "difficult", "easy", "red", "blue", "business" etc

这些可用标签存储在表中，称为包含所有可用标签的标签。

These available tags are stored in a table, call it "tags" that contains all available tags.

每篇文章都可以标记有多个标签，可以通过自定义管理界面进行编辑。

Each article can be tagged with multiple tags, editable through a custom admin interface.

将每个实体的标签简单地捆绑到字符串化的数组中可能会很诱人。每个标签的ID并将其与文章记录一起存储在我的文章表中：

It could be tempting to simply bundle the tags for each entity into a stringified array of the IDs of each tag and store it alongside the article record in my "articles" table:

id | title | author | tags
---+-------+--------+-------------
1  | title | TG     | "[1,4,7,12]"

尽管我确信这是个坏主意出于多种原因，是否有合理的理由进行上述操作？

though I'm sure this is a bad idea for a number of reasons, is there ever a reasonable reason to do the above?

推荐答案

我认为您应该阅读有关数据库规范化，然后自己决定。简而言之，您的提案存在很多问题，但您可能会决定接受它们。

I think you should read about Database normalization and decide for yourself. In short though, there are a number of issues with your proposal, but you may decide you can live with them.

最明显的是：

1. 如果在行（1）中添加了附加标签怎么办？您是否必须首先解析，检查是否已经存在，然后将其更新为 tags.append（newTag）。


2. 更糟还在删除标签？存在搜索标签，然后重新创建标签。


3. 如果标签要更改名称怎么办-可能需要进行一些审核？


4. 再次糟糕，不同的人以不同的方式指定标签名称又如何-很难合理化。


5. 如果要基于标签查询数据怎么办？您的查询变得比原本要复杂得多。


6. 介绍：客户端必须解析标签才能使用它。分隔符字段呢？改变这一点，所有客户都必须改变。

1. What if an additional tag is added to row(1)? Do you have to first parse, check if it's already present then update the row to be tags.append(newTag).
2. Worse still deleting a tag? Search tags, is present, re-create tags.
3. What if a tag is to change name - some moderation process, perhaps?
4. Worse again, what about dfferent people specifying a tag-name differently - it'd be hard to rationalise.
5. What if you want to query data based on tags? Your query becomes far more complex than it would need to be.
6. Presentation: The client has to parse the tag in order to use it. What about the separator field? Change that and all clients have to change.

简而言之，所有这些操作变得更加困难和繁琐。规范化旨在克服此类问题。做您所说的话，IMO的唯一原因可能是您一次就捕获了数据，并且它仅是信息性的-也就是说，对用户有意义，但对系统本身没有意义。这有点像在说最好避免这种情况（同样，IMO）。

In short, all of these operations become harder and more cumbersome. Normalization is designed to overcome such issues. Probably the only reason for doing what you say, IMO, is that you're capturing the data as a one-off and it's informational only - that is, makes sense to a user but not to a system per-se. This is kind of like saying it's probably best avoided (again, IMO).

这篇关于将数组存储为字段值或将数组值存储为记录是一个好主意吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！