What is best solution to implement a list of tags in SQL? [duplicate]

Question

I want to add a new column that contains a list of tags to an existing table named QUESTION.

Example:

QuestionID	Tag's List
201	SQL,UNION
823	C++,TEMPLATE,CLASS

I see 2 choices

1. I create a new column with sufficient spaces to contain the list

    ALTER TABLE question
    ADD tags_list VARCHAR(400); 

2. I create a new table for containing these tags (one tag per record)

    CREATE TABLE question_key
        ( questionID INT 
        , tag        VARCHAR(40)
        )

questionID	tag
201	SQL
201	UNION
823	C++
823	TEMPLATE
823	CLASS

The tags to add are similar to tags used on StackOverflow.

For each row in table, it is possible to have at maximum 10 tags smaller that 40 characters.

Just for information, SELECT criteria related to these keys will be

SELECT * WHERE key-list CONTAINS 'key1'
SELECT * WHERE key-list CONTAINS 'key1' OR 'key2'
SELECT * WHERE key-list CONTAINS 'key1' AND 'key2'
SELECT * WHERE key-list CONTAINS 'key1' BUT NOT 'key2'
SELECT * WHERE key-list CONTAINS 'key1' BUT (NOT 'key2' AND NOT 'key3')
SELECT * WHERE key-list CONTAINS-ONLY ('key1','key2','key3')

I know that I can use REGEX for solution 1 and JOIN, VIEW, GROUP BY or PARTITION BY for solution 2.

What is the best solution to implement ? Why ?

At SQL level, I think that solution 2 is the best, but for simplicity I prefer solution 1.

Answer 1

I suggest Solution 2 for a couple of reasons

1. Imagine your table growing and queries which cannot use index growing slower and slower. Solution 2 will allow for efficient queries. You could actually write filtering queries directly on question_key mapping table without bringing in data from question table columns upfront. Likely the queries in solution 1 are non sargable.
2. Imagine when you need to remove a tag altogether (like burnination in SO) or update the name say from sql-server to MS-Sql-Server. It is more easily done on solution 2.