Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
1  
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) –  David Spillett Jul 14 '13 at 8:42
    
Yes, your right. I updated my answer. –  Oskar Jul 14 '13 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 Jul 14 '13 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 –  Oskar Jul 14 '13 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 Jul 14 '13 at 21:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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