I am learning about database design and I'm writing a Java GUI program with a back-end database. The main table in my database stores a different product in each row. I have some columns such as: product_id (PK), price, stock_quantity, etc. I also have eight columns that store the names of the file names for that product's images: img_file_1, img_file_2 ... img_file_8. The Java program uses those file names to find the images to display on the screen for that product.

Is this poor design? Should I be storing these file names in their own table, and adding a foreign key to that table in my main table?

The program works fine as is, but I want to make sure I am learning good habits.

3 Answers 3


If each image has a specific purpose then this would be OK but your column names should be more specific, such as img_thumbnail, though I would still use a separate table with columns that carry information about each images use.

The better design, especially if the images do not have specific well differentiated uses, is to follow first normal form and have the images in a separate table. This allows cleaner storing of extra data about the images should you need to later, and so forth.


I think it's better to let the images have their own table. The way I've solved it is by having 3 tables.

One for the products, another one for the images and the third for the connections.

  • The product table just stores information about the product and nothing about the images.

  • The image table just stores the image id and file extension (the image id is also the filename).

  • The "connections"-table stores image id and product id so that you know which image belongs to which product.

Though, if only one image can be connected to one product you only need two tables. The product table and image table. The product table is just like above but the image table now contains a third column, productid. Now you can easily see which image belongs to which product.

  • 2
    The third table is over-kill if an image will only ever relate to one product. This technique is what you need to maintain a many-to-many relationship where a product is associated with any number of images and an image may be associated with more than one product (an image showing a range of related products for instance) Commented Jul 14, 2013 at 8:42
  • Yes, your right. I updated my answer. Commented Jul 14, 2013 at 9:03
  • "The image table just stores the image id and file extension (the image id is also the filename)." So you would make the image id (the filename) the primary key? That makes sense because they will always be unique. (I had made a new AUTO_INCREMENT column for the primary key, but maybe I will change it to be the file name).
    – Bri
    Commented Jul 14, 2013 at 18:10
  • I think it works great, one less column to worry about. If you use this method you should have an auto_increment int column as the primary key which is the filename Commented Jul 14, 2013 at 18:13
  • When I insert the productid FK in the images table, how should I define the ON DELETE and ON UPDATE constraints for this FK? I am thinking I should set it to ON DELETE CASCADE (in order to remove the images row when the product is deleted) but I am not sure what to set the ON UPDATE to, because I don't ever see productid in the products table ever changing. Should I therefore set it to ON UPDATE NO ACTION or ON UPDATE RESTRICT?
    – Bri
    Commented Jul 14, 2013 at 21:50

In general, if files are not supposed to be directly accessible by a URL (even with web server level redirects), put the files in a database. For example, you might run a scholarly journal and charge money for important articles. In this case, you do not want to store a URL because your product could be ripped off.

For images, there are times where simply storing the URL might be better. But, do you want anyone to be able to get to the image data without seeing it in context (i.e., getting a picture without seeing the HTML that is supposed to come with it). If not, put the file in a database.

Make a table for your products.

Make a table for your images.

Make a third, join table, that associates productId and imageId.

The three table solution (products, images, productImages) helps you avoid the "mopping yourself into a corner scenario." You just never know how many images/files you might have to associate with a record (be it a product, be it a user/member). The requirements of your application might change.

The third, join table (productImages: productId, imageId), solution makes it so you do not have to know (within reason) in advance how many images are associated with a record (products, members, etc ...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.