[RESOLVED] Save keywords in useable mysql format?

Yari

Hi,

Been looking for a easy solution to a fairly common problem: how do I structure a MySQL table for searchable keywords in the format: apples, bananas, grapes etc?

I want to be able to add keywords in a Textarea feild to a photo to make it searchable... should I split up the keywords in the database, or have them in one long record?

What I am looking for is a fast (to search) MySQL table structure.

Should it be:

photo_search_db

record_id = 1;
photo_id = 1;
photo_keywords = "apples, bananas, grapes";

photo_search_db

record_id = 1;
photo_id = 1;
photo_keywords = "apples";

record_id = 2;
photo_id = 1;
photo_keywords = "bananas";

record_id = 3;
photo_id = 1;
photo_keywords = "grapes";

Thanks Guys!

paulnaj

Assuming that ...

1) Any particular image will have possibly more then one keyword
2) Any particular keyword can be applied to more than one image

... then this is a many-to-many relationship.

Photos table:
photo_id        photo_url (for example)
1		fruit.jpg
2		room.jpg
3		kitchen.jpg


Keywords table:
key_id	key_value   

1	apples
2	bananas
3	chairs
4	tables



Relationship table:
photo_id	key_id
1		1
1		2
2		3
2		4
3		1
3		2
3		3
3		4

Hope that helps!

Paul 🙂

Yari

Ah, thats great... A bit more work to implement but seems very sensible.

Thanks for your time and expertise!