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

问题描述

说我提供用户检查她说的语言并将其存储在数据库中。重要的一点，我不会搜索数据库中的任何这些值，因为我将有一些单独的搜索引擎进行搜索。
现在，存储这些值的明显方法是创建一个表，如

  UserLanguages 
（
 UserID nvarchar（50），
 LookupLanguageID int 
）
  

该网站将负载高，我们正在尝试在可能的情况下消除任何开销，所以为了在UI上显示结果时避免与主成员表的连接，我正在考虑在主表中存储用户的语言，并带有逗号分开，像12,34,65

再次，我不搜索它们，所以我不用担心在该列上要做全文索引。

我没有真正看到这个解决方案有任何问题，但是我忽略了什么？

谢谢，
Andrey

解决方案

您现在可能不会丢失任何东西，但是当您的需求变更时，您可能会对该决定感到遗憾。你应该按照你的第一个本能建议来保存它。这是正确的方法。

你所建议的是一个经典的过早优化。你还不知道这个加入是否会成为一个瓶颈，所以你不知道你是否真的在购买任何性能改进。等等，直到你能描述这个东西，然后你会知道这件物品是否需要优化。

如果是这样，我会考虑一个物化视图，或一些另一种方法是使用归一化数据预先计算答案，而不考虑记录簿。

更一般地说，有很多可能的优化如果有必要，可以按照您建议的方式妥协您的设计。

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
)

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?

Thanks, Andrey

解决方案

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屋！