DB Design: multiple values for single field in a record

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
--------------------------------------------------开发者_StackOverflow社区----------------------------
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.

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 wordid field on this table as a primary key (identifying the word) and a many to many table to connect words with users (words_to_users with a wordid and userid fields).