I have a pretty decent grasp of Access and a bit about SQL. Someone asked if I'd make a small app where they could enter in a product code and an image of the product pops up and he wants to be able to see the product from different sides(ie front,back left and right). And he needs to be able to add items and associate images with them without me being on-call all the time. K, so i can sorta figure this out for myself but I also know that storing images through the database itself(well with Access at any rate) is not such a good idea. So i guess my question is, whats the best way to proceed? I know access isn't the greatest but would using MSSQL 2005(the version i have) make this any easier?

This post has been edited by Damage: 19 Aug, 2008 - 09:18 PM

Well if the application is for a business of some sort or if the user is looking to expand on this into something a bit bigger (like a full blown website with shopping cart etc) you will want to go with a more industrial strength database like SQL Server. Plus Access is not good for online websites because it can get hit pretty hard by multiple users and have problems.

As for not storing images directly in the database, you are absolutely correct. What you will want to do is store a path to the file, stored on the hard drive, in a field of the table. Whenever they query the item, you use the path to then display the image.

If they want multiple images of the product, then you will need to construct a separate database table, link it back to the products table and store several image paths in their own individual records in this table.

product
-----
id
product_name
product_details
date_added


product_images
-----
id
product_id
pathtoimage


The two tables above link to one another through the product.id = product_images.product_id fields in a one to many relationship. One product can have zero to many images in the product_images table.

This will give you the different angle shots for a given product and should work out nicely for you despite the database type.

Enjoy!

"At DIC we be product picture code ninjas... amadeus takes pictures of canada, I take pictures of disasters, and capty takes pictures of his own butt"

thanks for the info.

martyr2..... why wouldn't he be able to use the "image" datatype in SQL Server 2005 to store the image? it's not that difficult getting it in and out of the database.

i don't know if this is the reason martyr has for recommending that i store the path instead of the image but as far as i understand it, there are a few disadvantages to storing in DB, mainly(and someone please correct me if i'm wrong) that there is a significant decrease in performance when processing an image stored directly in the DB(and i'm referring to a lot of images)

Damage is correct, there is a significant performance hit storing images directly in the table records themselves. Databases do not do a very good job of managing binary data like this and inefficiently use disk space when storing them. They can also slow down queries and they cannot lend themselves to easy searching in the future. But the biggest drawback is that they bloat the size of your database and reduce performance.

I think in the future they may just get rid of some of these "blob" style fields in relational databases for the very reasons mentioned above.

I hope that answered your question.

There is on the otherhand also one significant advantage.
Securing the images.
If you have all your images stored directly oun your webserver and just use the path to them then what is there to prevent for random people to link to your images directly?
If you want to prevent that you need a way to store the images outside of your website and serve them for example through a script. In for example php you can do such a thing in 4 lines of code (using the passthrough function if i remember correctly)
For the php script to obtain the image data you need to either place it outside of the webroot and have the php script access it there, or store it in the database.
Since you probably need to check the database anyway to see if the user can access the image....

I think you get my drift.
Personally i would still chose to store only the (partial) path in the database and use the image storage outside of the webroot, since binary data is not something you should clobber up your database with.

sorry i forgot to mention that it's for use on a local network only