Posted by Koncept on 10/20/06 23:10

In article <1161320236.854242.143860@i3g2000cwc.googlegroups.com>,
kenoli <kenoli@igc.org> wrote:

> I'm interested in people's thinking about key word fileds. I will want
> to associate a number of key words with each person in a contact list
> using a mysql database. The key words will be selected from values
> derived from a lookup table and displayed in a form as check boxes or
> pulldown menus.
>
> I could either have a number of key word fields inserting one key word
> in each field or concatenate the words in one field. If I add key
> words I can append them to the one field. Any opinions about which way
> to do?
>
> I will of course want to search on these key words.
>
> Thanks,
>
> --Kenoli

I would keep your keywords in separate table (easier to query directly
) and then relate the id's of people in your database to these keywords
using a table which bridges people and keywords. (aka keep the keywords
in their own exclusive fields). Something like structurally:

CREATE TABLE `keywords` (
`id` int(11) unsigned NOT NULL auto_increment,
`keyword` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `lookups` (
`id` int(11) unsigned NOT NULL auto_increment,
`keyword_id` int(11) unsigned default NULL,
`person_id` int(11) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `persons` (
`id` int(11) unsigned NOT NULL auto_increment,
`first_name` varchar(30) NOT NULL default '',
`last_name` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;


Then to get keywords that relate to a particular member, you can do
this:

SELECT keywords.keyword
FROM persons
INNER JOIN lookups ON persons.id = lookups.person_id
INNER JOIN keywords ON lookups.keyword_id = keywords.id
WHERE persons.id = [the person's id]
ORDER BY keywords.keyword ASC

Would be interested how others would handle this...

--
Koncept <<
"The snake that cannot shed its skin perishes. So do the spirits who are
prevented from changing their opinions; they cease to be a spirit." -Nietzsche

[Back to original message]