SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member Q.E.D.'s Avatar
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a way to link keywords to hierarchical groups that inherit parent keywords?

    Hi,

    I'm trying to create an inherited keyword structure in MySQL, and I'm not sure the best way to proceed. Essentially, I need one table attaching keywords to groups, ie:
    create table keywords (
    varchar(255) keyword,
    int(6) groupID
    );
    where the same keyword can apply to multiple groups and groups can have multiple keywords, as a many-to-many relationship.

    Additionally, I need some structure for the groups that allows each group to inherit all of the keywords of its parent group. So far, I'm working with a table like:
    create table groups(
    int(6) groupID autoincrement,
    varchar(255) groupname,
    int(6) parentgroupID,
    PRIMARY KEY (groupID)
    );

    The intent is that if a keyword is changed in a parent group then all of its children, grandchildren, etc. inherit that change.

    I've tried joining the groups table to itself recursively, but I don't know how to do that in SQL without hard-coding the number of joins.

    Any ideas how to set up that sort of inheritance structure?

    Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Q.E.D. View Post
    I've tried joining the groups table to itself recursively, but I don't know how to do that in SQL without hard-coding the number of joins.
    that's the unfortunate side effect of using the adjacency data model (the one which uses a "parent id")

    but most applications can realistically set an upper limit on the number of levels, and then code that number of joins using LEFT OUTER JOIN for the retrieval query

    if you really can't write a query for the maximum number of levels, you'll have to either change your data structure to the nested set model (the one with "lft" and "rgt" columns), or else call the database recursively (which can be horribly inefficient)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member Q.E.D.'s Avatar
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, r937!

    I don't know what you mean by the nested set model. How does that work?

    Thanks again!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Q.E.D. View Post
    I don't know what you mean by the nested set model. How does that work?
    how hard did you try to search for this?

    there's even a sitepoint article which explains it

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

  5. #5
    SitePoint Member Q.E.D.'s Avatar
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, r937. I didn't realize that this was an established method.

    Stepping out of the structure for the big picture, what I'm trying to do is set up a way for a user to select the species for a picture they are uploading to a natural history site. When they pick a species the image gets all of the inherited tags like:

    Group: Crotalus atrox
    Keywords: Crotalus atrox, western diamond-backed rattlesnake, western diamondback rattlesnake, Víbora-cascabel de diamantes
    Parent Group: Crotalus
    Keywords: Crotalus, rattlesnakes
    Parent Group: Crotalinae
    Keywords: Crotalinae
    Parent Group: Viperidae
    Keywords: Viperidae, pit vipers, vipers
    Parent Group: Serpentes
    Keywords: Serpentes, serpente, serpents, snakes
    Parent Group: Squamata
    Keywords: Squamata
    Parent Group: Reptilia
    Keywords: Reptilia, répteis, reptiles, Reptiles
    Parent Group: Vertebrata
    Keywords: Vertebrata, vertebrado, vertebrates, vertébrés
    Parent Group: Chordata
    Keywords: Chordata, chordates, cordado
    Parent Group:Animalia
    Keywords: Animalia, animal, animals

    so the picture gets whatever keywords are assigned to it as well as all of the following keywords:
    Crotalus, rattlesnakes, Crotalinae, Viperidae, pit vipers, vipers, Serpentes, serpente, serpents, snakes, Squamata, Reptilia, répteis, reptiles, Reptiles, Vertebrata, vertebrado, vertebrates, Chordata, chordates, cordado, Animalia, animal, animals

    and if any of the parents have changes in keywords this is reflected in all of the children.

    The problem I see with the nested set model is that there would be hundreds of thousands of groups, each with multiple keywords. Do you have any suggestions on how MySQL could efficiently deal with that sort of set up?

    Thanks again!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hundreds of thousands is no big deal, i would only start worrying if it were hundreds of millions
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member Q.E.D.'s Avatar
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just found the article you referred to and I'm converting my table.

    Thank you for your help!


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
  •