I need to create a database in which several tables have images.For example, users have profile pictures and uploaded ones and products have many pictures, as well.Is it better to have one MySql table images or add images to every table that contains them?Also, how (syntax questions don't belong here but it would be nice) would you add many images into a MySql data column?I've read about the advantages of storing the file paths vs the actual files in the database but what about a long string with many file paths vs many rows?For example INSERT INTO images (userid, image1, image2, image3) VALUES (autoincrementedPK 'pathtofile1', 'pathtofile2', 'pathtofile3') orINSERT INTO images (userid, images) VALUES (autoincrementedPK, 'pathtofile1, pathtofile2, pathtofileN')Which option is better and why? Is option two viable? and if so, how would you implement it?
Creating a separate table for images or adding image fields to many tables?
database;database design;mysql;image;blob
First about storing file paths:Having several image path columns (imagepath1, imagepath2 ... imagepathN ) violates 1NF.Storing several comma-separated image paths in the same column also violates 1NF.When you violate the simplest of normal forms, you will have a lot of headaches in the future.The correct thing to do is create a separate table for images. That table would have a FK with the user table (userid). You then insert into that table as many image paths as you want to any given user.Regarding storing actual images as BLOBS.Same rules about NF1 apply. Same separate table for images applies.In this case having separate tables for BLOBS is good because the DBA can put the image tables in a separate tablespace, optimizing the disk IO.Even in the case users are allowed only one image, denormalization by creating a separate table with a one-to-one relationship is recommended.Note: I can't give you syntax because I'm not familiar with MySQL datatypes.