August 22nd, 2000, 06:40 PM
I want to associate keywords with images in my image DB. Im wondering what table structure would be best.
There would be around 10 keywords per file. I want to be able to perform searches such as:
.all images with keywords 'mike'
.all images with keywords 'mike' and 'jane' and 'tom' and 'chloe'
Do i have:
|filename | kw01 | kw02 | kw03 | ..
or something like
|filename | keywords |
im thinking about simplicity, basically the best way to do it.
[newbie to all this]
August 22nd, 2000, 06:43 PM
I'd suggest 2 tables
1) your images, with related info
2) a keyword lookup table with only two feilds, keyword and imageID
this is scaleable as you can have, say 50 keywords for one image and only 2 for another, it'll also make searchng much easier
take a look @ the article about DB normalising on phpbuilder.com
August 22nd, 2000, 06:58 PM
Ok, my current design is pretty much along the lines of whatyou suggest but..
I was thinking about when i have to search. I confused as to how i would perform my search if the keywords 'mike, jane, matt' where all under keyword.
To put it another way, how would i perform the substring search on the keywords for an image to see if they contained the names i was looking for.
If there was only one word i would use "select filename from table where keyword='mike'" but because keywork now contains more than one name ('mike','tom','jane') im a little confused.
Am i making myself clear? Hmm, i guess we'll see..
thanks in advance.