Storing keywords

[VAMick]

What is the best way to store keywords in a database table.
I have a document, and want to be able to first, register various keywords associated with the document, and then have a page where the user can type in keywords to do a search for matching documents. Then they would eventually select one document to view based on the keyword search.

So....back to storing the keywords. Do I store them all in one field, comma seperated, or is there a better method?

Thanks in advance.

 

[guelphdad]

the better method is to make a keyword table.

in your main document table you would give an id to the document say we are working with two documnents 937 and 938.

now in your keyword table you would store a row for each keyword.

id keyword
937 milk
937 water
937 bananas
937 grapes
938 engine
938 wheels
938 carburator

you would then do a join on your document table to search the document and your keywords.

select
documents.id,
documents.body
from documents
inner join keywords
on documents.id = keywords.id
where keyword in ('carburator','engine')

you would then retrieve document 938

 

[VAMick]

Ahhh, I get it. Excellent post, thanks much.

 

[guelphdad]

Oh, one other point I forgot, though this might be helpful in other places as well. Say you want only documents that have both carburator and engine in them instead of those containing only one of them, you can modify the query to:

select
documents.id,
documents.body
from documents
inner join keywords
on documents.id = keywords.id
where keyword in ('carburator','engine')
group by
documents.id,
documents.body
having count(*) = 2