What is wrong with this SQL statement for inserting concatenated IDS from a table into a field?

Question

INSERT INTO Activity_Feed (userID,Type,DataIDs,PodID) 
VALUES ( 1437
      , 'eventattend'
      , (SELECT LEFT(EventID, LEN(eventID) - 1 as nvarchar) 
         FROM ( 
            SELECT EventiD + ', ' 
            FROM   events 
            FOR XML PATH ('')) c (EventID))
      , 5)

Basically I want to take a bunch of IDs from a table and insert them as a comma delimited string into a varchar field.

E.g.

Activity_Feed (table)

activityID   1
userID       2
DataIDs      1,3,4,56,367 // This would be the (Select Ids FROM bit)

I want to take a bunch of RSVP IDs from a table and stick their IDs in the field...

To further explain I wanted to avoid normalizing the query because of the nature of this query. Let me know if I should still separate out the data...

The activity feed works like this...

I create a new entry in activity with a type of event_attending (which is an event I am attending.

I timestamp it, I enter the ID for the event in the dataIDs field any new activity matching event_attending in a 6 hour period fires an update record rather than insert

I keep the timestamp the same but update the ID's that are associated with that time period so basically update the IDs with the latest event attendance within that time period.

I thought normalizing seemed like overkill :D is there such thing as overkill with normalization?

Never insert comma separated values into a column. You are just making them harder to retrieve later. — PRNDL Development Studios, Nov 01 '12 at 16:40
Please see [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad) — Alex K., Nov 01 '12 at 16:46

John Woo · Accepted Answer · 2012-11-01T16:55:53.390

4

Always normalize your database.

This is totally not wrong but very poor in database design.

Reasons why this is very poor:

hard to join tables
hard to find values

Why not create table like this,

Activity

ActivityID
ActivityName

Feed

FeedID
FeedName

Activity_Feed

FeedID
ActivityID

so Activity_Feed table will contain something like this

FeedID  ActivityID
=====================
1         1
1         2
1         3
2         1
2         3
3         2
3         1

and you can now join the tables,

SELECT  a.ActivityName, c.FeedName
FROM    Activity a
        INNER JOIN Activity_Feed b
            ON a.ActivityID = b.ActivityID
        INNER JOIN Feed c
            ON b.FeedID = c.FeedID
-- WHERE  a.ActivityName = 1             -- or something like this

edited Nov 01 '12 at 16:55

answered Nov 01 '12 at 16:40

John Woo

258,903
69
498
492

Thanks for that. I will accept this as the answer as I now realise the benefits of normalizing the data rather than trying to bodge stuff and validate it later! – Chris Dowdeswell Nov 01 '12 at 16:52
Could you recommend some database logic for this for me in your answer? It would really help. I was going to just create another table called activity_items which will then add in items for that time period. :) – Chris Dowdeswell Nov 01 '12 at 16:55
1

@ChrisDowdeswell updated the answer with proposed schema :D and sample query. – John Woo Nov 01 '12 at 17:01

What is wrong with this SQL statement for inserting concatenated IDS from a table into a field?

1 Answers1