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

Databases
1

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 ([INDENT]varchar(255) keyword,
int(6) groupID[/INDENT]);
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([INDENT]int(6) groupID autoincrement,
varchar(255) groupname,
int(6) parentgroupID,
PRIMARY KEY (groupID)[/INDENT]);

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

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)

3

Thanks, r937!

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

Thanks again!

4

how hard did you try to search for this?

there’s even a sitepoint article which explains it

:smiley:

5

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

hundreds of thousands is no big deal, i would only start worrying if it were hundreds of millions

7

I just found the article you referred to and I’m converting my table.

Thank you for your help!