Database layout tagging system

Question

I am creating a web site for a customer and they want to be able to create articles. My idea is to tag them so I am going to implement the system.

What is the best design, both from an architectural and a perfomance perspective:

1. To have table with all tags and then have a one to many relationship table that links a tag like this:

articles table with ID
tags table  with ID
one to many table with columns Article.ID and Tags.ID

2. To have one table with articles and one with tags for articles like this:

articles table with ID
tags table with Article.ID and tag text

Thanks in advance!

Answer 1

Your first option is the most appropriate and theoretically right.

Guess, your clients do not think tags like a nice feature to have because everybody has it - they would like to have search by tags. Even if they don't yet understand their needs and really want to have tags because everybody around has them - they will realize their needs soon.

First option will give you better search operation performance.

Implement separate table for articles, tags and many-to-many between them.

Answer 2

Definitely the first option.

Apart from the other benefits, you could enforce some regularity in using tags, by checking if the tag (or a similar one) is already present before adding it, allowing users to select from existing tags, and/or allowing only superusers to add new tags.

This way you avoid mispellings or alternate spellings of the same tags (i.e. US, USA, USofA, U.S.A., U.S, US., America, Amerika, Amrica and so on when labelling something about the United States)