Database design - large fields

Let's say, for example, that I have a list of articles in a blog. Each article has one image, each image has one thumbnail.

When dis开发者_运维问答playing a list of articles, each will be shown with the thumbnail; when showing a single article, it will be shown with the full-size image.

This means I have three pieces of large (unknown size) data items with each article: image, thumbnail and text.

What are the pros and cons of these designs:

1. Articles table includes Thumbnail column and Image column
2. Articles table includes Thumbnail column, Image stored in separate table
3. Thumbnail and Image stored in a single separate table
4. Thumbnail and Image stored in their own separate tables
5. Site has write-access to a folder where Images and Thumbnails are stored, database contains URLs/filenames

(any I haven't considered?)

If it makes a difference, and I don't think it should, the site will be written in Ruby/Rails, using Postgres or MySql.

IMO, option 5 is the best. Just because you can store images in a database, doesn't mean you should. Store the file location, and any metadata about the images in the DB. Store the images in the file system.

The 'cons' of storing the images in the DB is that you end up jumping through hoops to actually use the images in your app. Almost any library, script, add-on etc can use your images if they are in a directory without any special coding. Put them in a DB and you need to write code in order to provide access to use them.

The only benefit I can see is in a webfarm environment where multiple web servers are using a single (or replicated database), the pictures will be available to all web servers automatically, and data and images can be backed up all at once.

IMO, the cons far outweigh the pro's of using a DB to store images.

If i understand things correctly the database already automatically handles separation of large fields into separate "tables" depending on the datatype and size of the field. Google for "out of row storage" or something similar. There might be settings for this too.

That means that you shouldn't have to create separate tables for large fields yourself.

As for storing on disk instead of db i have no idea. I think that question has been asked many times before.