June 5, 2003 at 9:20 am
Recently we are working on a project that involves storing info about the user's interests. The user is allowed to choose multiple interests and we want to be able to query out any users to a specific interest.
Right now there seems to be two ways. Let's assume there are 1000 different insterests. One way is to have a varchar(1000) or char(1000) field with a "10101010..."-like string to mark the user interests and we can use CHARINDEX function for queries. The other way is to use 1000 bit fields. I would say the latter solution would be faster in executing the queries.
My questions are:
Which scheme do you think is better? Is there any other and more efficient solutions to achieve this?
Looking forward to your inputs.
June 5, 2003 at 12:15 pm
Or, neither of those solutions and normalize the data:
CREATE TABLE Interest
(
Code CHAR(1) NOT NULL
, Description VARCHAR(100) NOT NULL
)
CREATE TABLE [User]
(
UserCode INT NOT NULL
)
CREATE TABLE UserInterest
(
[User] INT NOT NULL
, Interest CHAR(1) NOT NULL
)
That way, you don't waste storage space where a user doesn't have any interests, and you can retrieve data in an efficient fashion, instead of "tricking" your queries with CHARINDEX or a REALLY long IN expression...
You can use something like this to retrieve your user's interests:
SELECT Interest.Code, Interest.Description
FROM UserInterest
INNER JOIN Interest on UserInterest.Interest = Interest.Code
WHERE UserInterest = @User
June 5, 2003 at 1:30 pm
Thank you jpipes!
This is what I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply