在数据库中存储多项选择值 [英] Storing multiple choice values in database

问题描述

假设我让用户检查她说的语言并将其存储在数据库中.重要的旁注，我不会在 db 中搜索任何这些值，因为我将有一些单独的搜索引擎进行搜索.现在，存储这些值的显而易见的方法是创建一个表，如

Say I offer user to check off languages she speaks and store it in a db. Important side note, I will not search db for any of those values, as I will have some separate search engine for search. Now, the obvious way of storing these values is to create a table like

UserLanguages
(
 UserID nvarchar(50),
 LookupLanguageID int
)

但该站点将是高负载，我们正在尽可能地消除任何开销，因此为了避免在 UI 上显示结果时与主成员表连接，我想在主表中为用户存储语言, 用逗号分隔，如12,34,65"

but the site will be high load and we are trying to eliminate any overhead where possible, so in order to avoid joins with main member table when showing results on UI, I was thinking of storing languages for a user in the main table, having them comma separated, like "12,34,65"

同样，我不搜索它们，所以我不用担心必须对该列进行全文索引.

Again, I don't search for them so I don't worry about having to do fulltext index on that column.

我真的没有发现这个解决方案有任何问题，但我是否忽略了什么?

I don't really see any problems with this solution, but am I overlooking anything?

谢谢，安德烈

推荐答案

您现在可能没有遗漏任何东西，但是当您的需求发生变化时，您可能会后悔那个决定.您应该像您的第一直觉建议的那样将其标准化.这是正确的方法.

You might not be missing anything now, but when you're requirements change you might regret that decision. You should store it normalized like your first instinct suggested. That's the correct approach.

您所建议的是经典的过早优化.您还不知道该连接是否会成为瓶颈，因此您不知道您是否真的在购买任何性能改进.等到您可以对事物进行概要分析，然后您就会知道该部分是否需要优化.

What you're suggesting is a classic premature optimization. You don't know yet whether that join will be a bottleneck, and so you don't know whether you're actually buying any performance improvement. Wait until you can profile the thing, and then you'll know whether that piece needs to be optimized.

如果是这样，我会考虑使用物化视图，或其他一些使用规范化数据预先计算答案的方法，该方法不会被视为记录簿.

If it does, I would consider a materialized view, or some other approach that pre-computes the answer using the normalized data to a cache that is not considered the book of record.

更一般地说，如有必要，可以进行许多可能的优化，而不会以您建议的方式影响您的设计.

More generally, there are a lot of possible optimizations that could be done, if necessary, without compromising your design in the way you suggest.

这篇关于在数据库中存储多项选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！