Saving multiple items in one database cell

Question

I have a list of countries. Each user can check several countries. Once saved, this “list of user countries” will be used to determine whether other users will be in the countries selected by the specific user.

The question is what would be the most effective approach to this problem ...

I have one, one for saving the user's selection in the form of a limited list, such as Canada, USA, France ... in one field varchar (max), but the problem with it will be that as soon as a user from Germany logs in page, I am performing this check. To search for Germany, I would need to get all the items and delimit each field to check their value or use sql 'like', which again is pretty slow.

If you have a better solution or some advice, I would be happy to hear.

Just make sure that many users will have their choice of countries from which and only they want users to land on their page. So far, millions of users have reached these pages. Thus, a faster approach would be better.

MSSQL and ASP.NET

thank

Answer 1

You cannot save a list of values ​​in one cell. Consider having a separate table in which each of the selected countries stores a link to a foreign key for the user table. This is standard database normalization .

PLEASE do not omit the route you are thinking of by storing multiple entries in one field. I had to rewrite more applications due to poor database design than for any other reason, and this is poor design.

Added

I have this poster on my wall at work: http://www.informationqualitysolutions.com/FreeStuff/rettigNormalizationPoster.pdf

DB Design, . , . , .

Answer 2

. normalized.

"--":

UserId
CountryId

, ( SQL, ). .

Answer 3

UserCountries ( ), UserID CountryID. . , . !

Answer 4

I think it would be better to use a table UserCountrythat contains a link to the table Userand Country. This creates much more opportunities for querying the database. Examples of queries that are much simpler:

• Number of countries per user
• All users who have selected a specific country
• Sort all popular countries

Answer 5

Do not store multiple countries in one field. Add 2 more tables - Countries (ID, Name) and UserCountries (UserID, CountryID)