Creating clouds for articles

[djomla]

Hi,

Can you explain how you are dealing with tag clouds for articles.
Curently (and i'm not to happy with this). When user writes article he enters in tags that he would like for article (comma separated). Then i explode this tags and write them in separate table :

CREATE TABLE `articlestags` (
`articletagID` INTEGER(150) NOT NULL AUTO_INCREMENT,
`articleID` INTEGER(11) NOT NULL,
`tag` VARCHAR(200) COLLATE utf8_general_ci NOT NULL DEFAULT '',
)


Then when i want to create cloud i search this table for designated tags and get articles that have same tags. Is this method ok? Or maybe someone have a better idea?

[eeight]

The idea is fine--I've been using it with my cartoons for quite some time and, thus far, have not found a reason to change to a different method.

However, if I'm not mistaken, the table can be made more efficient by normalization. That is, by splitting it into two separate tables: TAGS and ARTICLES_TAGS.

TAGS
tag_id (Primary Key, Auto_Inc)
tag

ARTICLES_TAGS
articles_tags_id (Primary Key, Auto_Inc)
article_id (Foreign Key)
tag_id (Foreign Key)

So TAGS just lists all the tags being used and ARTICLES_TAGS says which tags correspond with which articles.

[Dr John]

I'd suggest

articletag {
article_id integer,
tag varchar(xx)
}

with tag and article_id as the joint primary key. No redundant joins to a separate tags table is required. A tag is a unique item and doesn't need a numerical id to force a join to tell you that the number stands for a unique bit of text (eg css is as uniquely described by the single field tag=css as it is by the two fields id=7, tag=css).

The same is true for colours eg red, orange yellow etc do not need a numerical id of 1, 2, 3, then a join to extract the word red, or whatever. Of course a colour may also have another attribute like #123abc as in lists of named colours.

If you wish to offer users a list of acceptable tags to use, you could have a look-up table with a single field of tag words to populate a drop-down list in a form.

[r937]

and if you ~don't~ want to present users a list of tags from which they must choose, but, instead, want to let them use whatever tags they want, then you should not bother with a tags table at all, and just do this --

CREATE TABLE articlestags 
( articleID INTEGER NOT NULL
, tag VARCHAR(200) COLLATE utf8_general_ci NOT NULL 
, PRIMARY KEY ( articleID,tag )
, KEY ( tag,articleID )
);

by the way, where did INTEGER(150) come from? just curious, as you shouldn't really be using an auto_increment here anyway

[r937]

No redundant joins to a separate tags table is required.

this remains true even when you want to enforce relational integrity with the tags table (i.e. ensure that only registered tags are used)

there's this myth that numeric keys are required for joins, but it jes' ain't so...

[eeight]

Thanks for the tips, DrJohn and r937. I see now that the use of a FK was redundant as I could have just as easily used the tag itself.

r937 - I had the impression that it's easier to search for rows by an integer than by a string, thus the use of id's. Why not here?

[r937]

r937 - I had the impression that it's easier to search for rows by an integer than by a string, thus the use of id's. Why not here?

because of the "search by" criteria

under what scenarios would you be searching for a tag by its tag_id? or for article_tags rows by tag_id? how did you know which tag_id to search for?

map out those scenarios, including the SQL for each step, and you will see