multiple rows or one collumn [sql performance question]

Question

Hi everyone. I'm struggling with this simple idea. Look at reddit, when you sign up for a new account.. you automatically subscribe to some default channels.

I want to do the same with my website.

I know I could do the simple and stupid user_chan with id, user_id, chan_id

if chan is like this :

ID | NAME
1  | videos
2  | pictures

user_chan would be like this (i'm user 1)

ID | USER_ID | CHAN_ID
1  | 1       | 1
2  | 1       | 2

I'm trying to be very clear here :D

I guess that's how reddit works. But everytime a user signs up, they must have to insert a dozen of rows. And I guess they have tons of users !!

So is a solution inside user table like this more clever ?:

ID | USER_NICKNAME | CHANS
1  | me            | 1,2

Answer 1

Do not use one column to store multiple values. This is a denormalization that will cause pain later. E.g., when you need to query which user has channel 3, you are going to have to use LIKE, which will perform badly. When you need to remove just one channel of the several the user has, it gets even harder.

With most databases, you can insert multiple rows with one INSERT statement. The exact syntax varies by platform; in SQL Server, you can do this:

insert into user_chan
(USER_ID, CHANID)
select 7634, 3
union all
select 7634, 27
union all
select 7634, 9

Answer 2

Or, use an XML column to store the multiple values as you suggested. This can have an XML index and can be queried / updated etc as you would a relational table (albeit more complex syntax).