Very simple database structure for a tag cloud

The tag cloud (or word cloud) is a visual representation of the word content of a given text: in plain English it’s a listing of the words having different sizes, those used more frequently being larger and those less used being smaller. It started out as a buzzword but quickly become largely used. I will not go now into endless discussions about the controversial value it adds to your websites.  Assuming you already decided you want one, let’s see a very simple method to store the tags into a MySQL table.

First we need to create the table. I named it “tags” and it only has two fields: one to store the word and one to store the number of times it was found in the given text.

 
CREATE TABLE IF NOT EXISTS `tags` (
`tag` text NOT NULL,
`count` int(4) NOT NULL DEFAULT '0',
UNIQUE KEY `tag` (`tag`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please note I added an UNIQUE index key to the ‘tag’ filed to make sure the same word doesn’t get inserted twice. Now we can do the insert in a single query:

INSERT INTO tags
SET tag = "new_word",
count =1 ON DUPLICATE KEY UPDATE count = count +1

The first time it will insert the word, the rest it will increase the counter.
Simple as I promised.