Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jul 2002
    Thanked 0 Times in 0 Posts

    Categorizing issue

    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

  • #2
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Thanked 0 Times in 0 Posts
    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 ...)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts