Ways to implement tags - pros and cons of each?

These solutions are called mysqlicious scuttle and toxi This article compares benefits and drawbacks of each.

These solutions are called mysqlicious, scuttle and toxi. This article compares benefits and drawbacks of each.

Ah yes, I just found this. Very helpful! – bobobobo May 21 '10 at 21:09.

I would argue that there is a fourth solution which is a variation on your third solution: Create Table Posts ( id ... , title ... ) Create Table Tags ( name varchar(30) not null primary key , ... ) Create Table PostTags ( PostId ... , TagName varchar(30) not null , Constraint FK_PostTags_Posts Foreign Key ( PostId ) References Posts( Id ) , Constraint FK_PostTags_Tags Foreign Key ( TagName ) References Tags( Name ) On Update Cascade On Delete Cascade ) Notice that I'm using the tag name as the primary key of the Tags table. In this way, you can filter on certain tags without the extra join to the Tags table itself. In addition, if you change a tag name, it will update the names in the PostTags table.

If changing a tag name is a rare occurrence, then this shouldn't be a problem. If changing a tag name is a common occurrence, then I would go with your third solution where you use a surrogate key to reference the tag.

Thomas: this is same as scuttle, only much harder to manage. Possibility to write UPDATE Tags SET TagName = 'newtag' WHERE TagName = 'oldtag' instead of UPDATE PostTags SET TagName = 'newtag' WHERE TagName = 'oldtag' is not really worth it. – Quassnoi May 21 '10 at 21:26 @Quassnoi - With Cascade Update you need only write Update Tags Set Name = 'NewName' Where Name = 'OldName'.

No harder to manage that if you use a surrogate key. The real question is whether the benefit of avoiding the extra join outweighs the frequency with which you are altering an existing tag name. Since I would assume the later is infrequent, the performance benefit is probably worth it.

– Thomas May 21 '10 at 21:33 @Thomas: again, how is it different from way 2 (scuttle) except having an extra table (Tags) which serves no purpose? – Quassnoi May 21 '10 at 21:38 @Quassnoi - The design is closer to Toxi than scuttle. The Tags table serves a very important purpose.It ensures that you cannot add a tag reference in PostTags that you not already have in the Tags table.

I.e. , you still maintain a single list of tags.In addition, it allows you add additional attributes on the tags just as you with with the Toxi setup. The difference is that if you want a list of posts for a given tag, you avoid additional work of having to join to the Tags table (you still can of course).

With thousands of rows that will make no difference but on millions it will. – Thomas May 21 '10 at 21:58 @Quassnoi - Keep in mind that on the Tags table in the Toxi setup, you are still going to want a unique constraint on the Name of the tag anyway and it will likely be non-nullable. You might as well use it as the primary key in that case unless you knew you would have many calls to alter a tag name which I would think is unlikely.

– Thomas May 21 '10 at 21:59.

I personally favour solution #3. I don't agree that solution #1 is easier to mantain. Think of the situation where you have to change the name of a tag.

Solution #1: UPDATE posts SET tag = REPLACE(tag, "oldname", "newname") WHERE tag LIKE("%oldname%") Solution #3: UPDATE tags SET tag = "newname" WHERE tag = "oldname" The first one is way heavier. Also you have to deal with the commas when deleting tags (OK, it's easily done but still, more difficult that just deleting one line in the taggings table) As for solution #2... is neither fish nor fowl.

I think that SO uses solution #1. I'd go with either #1 or #3. One thing to consider is if you have several thing that you can tag (e.g. Adding tags to both post and products, for example).

This may affect database solution.

Well I have the same doubt I adopted the third solution for my website. I know there is another way for dealing with this problem of variable-length tuples which consists in using columns as rows in this way you will have some information identifying the tuple redudant and the varying ones organized one for each row. +--------+-------+-------------------------------------+ | postId | label | value | +--------+-------+-------------------------------------+ | 1 | tag |C++ | +--------+-------+-------------------------------------+ | 1 | tag |search | +--------+-------+-------------------------------------+ | 1 | tag |code | +--------+-------+-------------------------------------+ | 1 | title | Why is a raven like a writing desk?

| +--------+-------+-------------------------------------+ This is really bad but sometimes it's the only feasible solution, and it's very far from the relational approach.

And yet it's quite easy to update and maintain, queries are slowed down but you might have the same time problem joining a huge number of tuples from 3 different tables, you might consider to use different indices to increase performances of certain types of queries. Hope this helped – urobo Jun 9 at 20:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.