Should the descriptive tags associated with an entity be stored in a separate database table?

Question

I have a Questions model, and just like StackOverflow, each question can be tagged with multiple descriptive tags by a user.

What I'm trying to decide is whether it's necessary for the Tags associated with a question to be stored in a separate table in the database.

Or could I store the Tags as a single field of the Questions table as a list of space-separated strings?

I'm not sure which makes more sense - is there any good reason to separate the data?

Answer 1

I would put the questions in 1 table, the tags in 1 table, and have a seperate table to connect the tags to questions. This would be the best way to build that database. It keeps all tags consistant and highly reduces redundency.

By seperating the data like this, your can assure that searching for a specific tag will bring back the same items. You don't have to worry about whether the tag is spelled the same throughout all the questions. Also, you can limit the tag options easier this way.

Answer 2

You should definitely store the tags in a separate table, it makes everything easier, and that's the whole idea of a 'relational' database.

Answer 3

Separating tags into their own table, plus a further table with a many:many relationship between Tags and Questions, is what's known in relational land ad "normal form". It makes it easier and faster to perform tasks such as getting all questions tagged with a certain tag, finding the most popular tags, &c.

(Just in case you don't know -- a "many:many relationship" is a table with just two columns [a foreign key into Tags and one into Questions] and no uniqueness constraints).

Answer 4

Using a comma-separated string for a multi-valued attribute is another SQL Antipattern. :-)

• How long does the string need to be? Stated another way: how many tags can a given entry have? (It depends on how long the individual tags are.)

• How do you account for strings that contain the separator character? What if a character you currently use as a separator becomes a legitimate character in a tag?

• How do you insert or delete elements from the list in SQL? (You have to fetch the whole list into the application, explode the list, filter through it, and re-post it to the database.)

• How can you do aggregates like COUNT(*) in SQL?

• How do you search efficiently for all entries that share a given tag? (You have to use costly pattern-matching queries.)

The solution is to use a separate table, as most other folks on this thread are advising.