Friday, February 10, 2012

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?
Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>
|||Yes, it would be much better to do as you describe.
David Portas
SQL Server MVP
|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

No comments:

Post a Comment