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.
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.
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
Make a table for your
Make a third, join table, that associates
The three table solution (
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 …).