Image Handling and MySQL suggestions requested

I am a new coder and I am wondering what some better options might be for handling images for my gallery website.

Currently here is how I am building the site to handle images:

  • Images are not stored in the database - they are just stored in an images folder on the server
  • I have an images table (imagesTable) that has 2 columns which contain the image file name (i.e. mypic.jpg), and the product number it is associated with. (Columns: image_name and product_number)
  • As there might be multiple images per product number, I have declared both columns in the images table to be primary keys.
  • On my products table (productsTable), I have several columns for the product details, and I have a column (product_image_name) for the “primary” display image for the product.
  • On my gallery display page, I test for a match on the images table with both of the product number and the image name, and then display that as the main image.
  • On the specific product page, I show the main image, and any additional images.

I am now wondering if this is the best approach? I had originally thought the best way would be just to have a third column in the images table for “image_id” which would be a unique identifier and primary key for the image. This would then mean that I change my main image column on my product table from “product_image_name” to “product_image_id” and link accordingly.

Currently I am just using phpMyAdmin for my database tools, until I fully create my admin pages, so I am being extra careful to ensure that my image name used in both tables is the same.

I originally created my database tables from a dump of data from Excel, and I found a couple typos in image names, so I had to go to both tables to update it. In theory, this should not be an issue once I create my admin page for adding/managing images, as I will ensure that it makes changes to both tables.

Any recommendations on how to best accomplish what I am trying to do (as a new coder) would be appreciated.

both methods are fine – you will see subtle differences when you code the sql queries, but you should not see any difference in performance

don’t forget to declare your Foreign Keys as well as PKs

  • Should it be possible for images to be shared between products? That’s not possible now, so if the answer is yes you’d need to change the tables
  • Could it never happen that you have 2 images with the same filename for a product? Again, if the answer is yes, you’d need to change the tables
  • (Pet peeve, opinion) why do you end your table names in Table? It is a table, there is no need to name it as such. Or do you also end all column names in Column? :wink:

Not related to the database here but I’d still like to point out that if it’s at all possible I would look into storing the images somewhere else than on the server, like in Amazon S3, and serve them from there. That way, if you ever get multiple servers (because 1 server can no longer handle all traffic) you don’t need to think about how to scale image serving, as you already have the solution in place.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.