I have been debating back and forth about which way is better to provide images for entries in a database. For instance, let's take the example of an ecommerce product. I have the product in a mysql database with a prod_id of 1. In the product table is also a description, price, etc. However this product has multiple pictures associated with it, including a "main" avatar type of picture. I could think of a few ways to associate the images, including thumbnails, with the product.
1. Create another table with the url as a text field and the prod_id as a foreign key. Then have another link somehow to a thumbnail url, either in another table or to a self linking reference. This method would require database manipulation and maintenance every time an image is added/deleted.
2. Work off of a directory structure and keep all of product 1's images in a images/prods/1 folder. But then I might have trouble linking the thumbnails unless I used another naming convention in the file name. This would be dynamic in the sense that I could just simple ftp images to the directory, but making sure the naming convention is strict will be a little bit of a manual task.
3. I guess a third option would be to go off of a directory structure again, but use the originals as thumbnails. Wouldn't this be resource intensive?
4. Is there an advantage to mixing the two? For instance, having a directory listing, but mapping the thumbs within a database entry?
I guess my main goal is to make something easy to maintain. I want the adding of images/products to be as automated as possible. I don't need help with the actual code, which is why I posted this in the Theory & Design part. I'm just wondering which one seems better and also which one is setting a trap for the future. Any opinions will be totally appreciated, unless your opinions are about Ethiopian politics in which case they might be only slightly appreciated.
Vin
Image Thumbnail Mappings
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Image Thumbnail Mappings
The database route is the most powerful in that you can create any associations you want. However, using the database adds an extra layer of complexity to the application -- and a need to keep the database and actual files in sync. Going with directory/file naming conventions is simpler and can usually handle everything you need. Usually some simple naming conventions can deal with a main image, other images, plus thumbnails.
One thing I might recommend is to create a class that deal with generating the URLs for you. You would give it an ID and then as for the main image, or associated images, or thumbnails, and it would give you the URLs. Then you can implement it in the simplest way first. If you need to change to a more complex system you can refactor the class without having to change anything else in your application.
One thing I might recommend is to create a class that deal with generating the URLs for you. You would give it an ID and then as for the main image, or associated images, or thumbnails, and it would give you the URLs. Then you can implement it in the simplest way first. If you need to change to a more complex system you can refactor the class without having to change anything else in your application.
(#10850)
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Image Thumbnail Mappings
There are some benefits to keeping image information in the database. For instance, storing metadata like captions, alt and title text, width and height of each image. Additionally, you might want to separate your images from specific content types so you can reuse certain uploaded images.
Here's the schema I've been using, though I'm not certain I like it:
Here's the schema I've been using, though I'm not certain I like it:
The original has a null parent id, and then each resized version's parent id is the id of the original.CREATE TABLE `images` (
`id` int(10) unsigned NOT NULL auto_increment,
`parent_id` int(10) unsigned NOT NULL default '0',
`filename` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`alt` varchar(255) NOT NULL default '',
`width` int(11) NOT NULL default '0',
`height` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;