Should I use a separate table to store image file names?
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.
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.
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 ...).