Saving multiple items per single database cell...

Question

Hi, i have a countries list. Each user can check multiple countries. Once saved, this "user country list" will be used to get whether other users fit into countries certain user chose.

Question is what would be the most efficient approach to this problem...

I have one, one to save user selection as delimited list like Canada,USA,France ... in single varchar(max) field but problem with it would be that once user from Germany enters page i perform this check on. To search for Germany i would be needed to get all items and un-delimit each field to check against value or to use sql 'like' which again is pretty damn slow..

If you have better solution or some tips i would be glad to hear.

Just to make sure, many users will have their own selections of countries from which and only they want to have users to land on their page. While millions of users will reach those pages. So the faster approach will be the better.

technology, MSSQL and ASP.NET

thanks

Answer 1

In this case, you want will want to create a table called UserCountries (or some such) which would store the UserID and CountryID. This is a standard relational construct. To beginners, it seems strange and too involved, but this structure makes it very easy and very fast to write flexible queries against this type of data. No delimiting required!

Answer 2

Do not save delimited fields into your database. Your database will not be normalized.

You need a many-to-many table for users and countries:

UserId
CountryId

If you do start using a delimited field, you end up needing to parse it (either in SQL or your Code). It is more difficult to query and optimize.

Answer 3

I think it would be better to use a UserCountry table, which contains a link to the User and the Country table. This creates a lot more possibilities to query against the database. Example queries that are much simpler this way:

• Number of Countries per user
• All users which selected a particular country
• Sort all popular countries

Answer 4

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