View Full Version : Categorizing issue

12-14-2004, 01:18 PM
I’m dealing with a little problem on the architecture of my MySQL database due to its complexity and I would appreciate any tips on the following:

I’m building a database for my image bank and want to categorize the images so that they may be viewed according with a specific multiple classification. What I have now is an image that belongs to a Category A and has a sub-category A1, for example: “People” / “Health”.

But I would like to have the image to be seen on a category “Concepts” with a sub-category “Stress” or even category “Health” and sub-category “Mental Health” or all of them, according to the user’s choice. If the user clicks on the category “Concepts” he will be able to see my image IMG_5454.jpg and also he could see that same image on the “Health” category.

I now have an “image”, “category” and “subcategory” tables. What could be the best structure for use with PHP to select images that meet the multiple classification?

Thanks in advance for any help

12-14-2004, 04:29 PM
i don't see what's so complex.
Your three tables should all have a primary key (PK)

You now create a 4° table that contains these PK's (so they are used as Foreign Keys).
Table: image_cat_subcat
imageID | catID | subcatID
1 | 1 | 2
1 | 3 | 2
3 | 1 | 5

you see? so the image with PK = 1 in the images table, is linked to the combination of categorie with PK = 1 and subcategorie with PK = 2.
but it's also linked to the combination of categorie with PK = 3 and subcategorie with PK = 2.
This new table, is caled a factstable and it only contains Foreign key valeus to your 3 existing tables (which are called dimensiontables)

if you then want to run a select to show all categories that image1 fals under, then you just run a
SELECT image_cat_subcat.catID, categories.categori_lable FROM image_cat_subcat INNER JOIN categories ON image_cat_subcat.catID = categories.catID WHERE image_cat_subcat.imageID = 1

or if you wanna get all images that are linked to categorie 1, then you run
SELECT image_cat_subcat.imageID, images.imagename FROM image_cat_subcat INNER JOIN images ON image_cat_subcat.imageID = immages.imageID WHERE image_cat_subcat.catID= 1

(well, the table and columnnames will be different of course ...)