SQL Image Fields

[Peter A.]

I need a second opinion from someone out there. I have a database with about
20 tables. Each of these tables need to store one or maybe more images for
each record. I cannot use a varchar field to point to an image file on the
server.

Therefore, I am trying to figure out if its better for me to put an image
field in each table OR make a table called IMAGES that has multiple Nullable
Foreign keys and use one table for all images. My dilemma is that each
record will have a lot of Null values because I will only be using one of
the foreign key fields for each record in the IMAGES table.

For example, an IMAGES table with the following fields:
ImageId int PK
FK1 int Nullable
FK2 int Nullable
FK3 int Nullable
FK4 int Nullable
FK5 int Nullable
ImageData image

Please help if you have some experience in storing images and files in a SQL
image field type.

--
Peter A.

[Erland Sommarskog]

Re: SQL Image Fields

Peter A. (pete208@insigh tbb.com) writes:[color=blue]
> Therefore, I am trying to figure out if its better for me to put an
> image field in each table OR make a table called IMAGES that has
> multiple Nullable Foreign keys and use one table for all images.[/color]

The latter solution is definitely something you should stay away from.
What if you need to add one more table? Then you must change this table
to add one more FK column.

If you want a central table for images, a better option would be to
have an imgid in all tables, and then to have that imgid point to
the images table. I'm not particularly enthusiastic about this either,
from a general data-modelling perspective, but it is possible that it
could have some merits for blobs.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[uiukin]

Re: SQL Image Fields

what about adding one more table to deal with the nulls ?
in the new table you'll enter the foreign keys to the tables where the
corresponding image is needed and foreign keys to Images table to
point the image.
that way the nulls will disappear.

hope it works for you :)