Keywords in MySQL

vigour

I'm building a picture database with PHP and MySQL. The administrator should be able to add a picture to the database and at the same time add up to 5 keywords describing the picture. For example: Dog, Car, Shop, Food, Train. It's up to the administrator to add these keywords when he needs them.

The user should be able to search the database for some pictures mathing the keywords. The user can only select the keywords that are in the list, typed in by the administrator. My question is:

What method is the best to store these keywords in the table?

In one field called keywords, separated by a comma?

Or in 5 different fields called keyword1, keyword2, keyword3, keyword4, keyword5?

How do I search from PHP?

sneakyimp

i would create a table that associates a single keyword with a single picture. heck, i'd probably create two tables...one for keywords and another to associate a keyword with one or more pictures.

KEYWORDS
id - integer
keyword - varchar

KEYWORD_PICTURE_ASSOC
id - integer
keyword_id - integer, connects to KEYWORDS.id
picture - points to the picture either by filename or by containing the id of yet another db table called PICTURES.

if a user wants to search, you can just SELECT * FROM KEYWORDS to get all the available words.

then you can run a join query to fetch the resulting pictures.

MarkR

Probably the best way to do it is store them in a TEXT column with a fulltext index, then use a MATCH query.

Read the MySQL docs for full-text indexing for more details.

Mark