3

I've been working on a SQL model for my website, and I'm really conflicted on how to store these images in my database for use around the site.

First, each profile will contain a relevant 10 images total. 4 screenshots, with thumbnails, and two extra images. These images will be used all around the site.

Is storing the image path for each image in it's own column fine? A buddy tells me that I should place all images in it's own table and cross-reference them for better performance. I'll be calling all sorts of information from the profiles around the same time with the images themselves though.

I'm just wondering what the common practice for something such as this is.

Again, I'm not storing the actual image in the database - just the image path.

Juha Syrjälä
  • 33,425
  • 31
  • 131
  • 183
Sebhael
  • 69
  • 1
  • 9

2 Answers2

5

Store images on your file system and store paths in database..

If profile has more than 1 images then create a separate table for images.

Profile Table:

id | name | etc | etc
---------------------
1  | abc  | etc | etc
2  | xyz  | etc | etc

Image Table:

id | profile_id |     image_url     | image_type
-------------------------------------------------
 1 |     1      | images/image1.jpg | screenshot
 2 |     1      | images/image2.jpg | other
 3 |     2      | images/image3.jpg | screenshot

Now you can create different functions to get images for specific profile. For example:

getProfileImages( profile_id, image_type=NULL ) {
  // run query by joining profiles and images tables.
  // return images paths
}
Naveed
  • 41,517
  • 32
  • 98
  • 131
  • Yes. I know. I'm asking what is the better way to store a series of them inside of the database to be used all around the site. Would storing each image path in it's own column be best? Storing each image in it's own row in a separate table, then referencing it from the profile table - be best? Or store each image separated with commas, then use serialization to display them... etc etc – Sebhael Jan 23 '11 at 14:30
  • Each image path in its own column... Well, I'd do in this way, but it all about your preferences and patterns. – Shoe Jan 23 '11 at 14:34
  • Alright, this helps clarify this a lot more. Thank you for the help. – Sebhael Jan 23 '11 at 14:40
0

If you are storing multiple images per profile, you should make an images table. This will cause a table join (usually poor performance) every time you look up the images for a profile, but keeps the database normalized.

You could create some other encoding, such as a comma separated list, but you will require processing for that as well, but string parsing is also usually poor performance, and might incur bugs.

I suggest you go with the separate table at first, and if you find that it performs poorly, attempt to optimize it.

nrobey
  • 705
  • 4
  • 12