数据库设计：记录中单个字段的多个值
我正在开发自动建议功能，作为其中的一部分，我想根据特定用户的偏好对结果进行加权。例如：如果我的大多数用户经常输入 fish，那么一旦用户输入 f，算法就会返回 fish 作为最流行的结果。但是，如果特定用户主要输入 food，那么我想应用一个权重，以便考虑该特定用户的偏好。
我最初想到通过使用一个大型自动建议索引来实现此目的，其中包含一个字段 userids ，每当用户输入字母时，算法就会检查该特定用户的 userid 是否存在于 >userids 字段，如果存在，则会对该特定结果应用相应的权重。
一些记录如下所示：
word |count |userids
------------------------------------------------------------------------------
food |2 |aa,b,ccd
fish |12 |a,b,c,d,e,f,gg,he,jkl,sd
但是，我认为这种方法即使在几百个活跃用户的情况下也无法很好地扩展。设计这个数据库的更好方法是什么？
提前致谢， 睡着的
武士我是数据库设计的新手，所以请用外行术语解释您的解决方案。
I am working on an auto-suggest feature and as part of it I want to weight the results based on a particular user's preference. For example: If most of my users frequently type in fish, then the algorithm will return fish as the most popular result once the user types f. However, if a particular user mostly types in food, then I want to apply a weight such that it takes that particular user's preference into account.
I initially thought of doing this by having a large auto-suggest index, with a field userids and whenever a user types in a letter, the algorithm would check if that particular user's userid was present in the userids field and if present would apply a corresponding weight to that particular result.
A few records would look like:
word |count |userids
------------------------------------------------------------------------------
food |2 |aa,b,ccd
fish |12 |a,b,c,d,e,f,gg,he,jkl,sd
However, I do not think this is an approach that would scale all that well with even a few hundred active users. What would be a better way to design this DB?
Thanks in advance,
asleepysamurai
P.S. I'm a newbie when it comes to DB design, so please explain your solution in layman terms.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这不是一个好主意。该表未标准化，当您需要加入此字段时，您最终会遇到复杂的查询。
更好的设计是在此表上有一个
wordid字段作为主键（标识单词）和一个多对多表来连接单词与用户（words_to_users和wordid和userid字段）。This is not a good idea. The table is not normalized and you will end up with complicated queries when you need to join on this field.
A better design is to have a
wordidfield on this table as a primary key (identifying the word) and a many to many table to connect words with users (words_to_userswith awordidanduseridfields).