patternMinor
Should I use a separate table to store image file names?
Viewed 0 times
imagefilestoreseparatenamesshouldusetable
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#46324, answer score: 4
Revisions (0)
No revisions yet.