SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating clouds for articles

    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?

  2. #2
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dr John View Post
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eeight View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •