Very Computer

I'm in the process of trying to design a database with SQL Server 6.5 (to be
upgraded to 7.0 by December) which will contain photos of each employee in
the table.  I've read somewhere that I shouldn't have a column where I store
the employee's photo along with the rest of his record.  That I should link
to it (in another table??).

I know this newsgroup is frequented by some hefty experts so hopefully some
of you will be able to guide me on the right track.  This SQL Server will be
connected to the Internet if that makes a difference in my scenario.  If I
shouldn't put my images in the same table as the rest of my employee
information, what are the design strategies I should take for the images?

I am new to SQL Server 6.5, so feel free to also explain the steps needed to
accomplish something.  Thus far, this has been a beneficial newsgroup and I
thank you all in advance for your assistance.

Best,
Mike

Mike - You can store the image in the table using the image data type
however its generally more efficient to store the file path to the image in
the table and have the client side tool use the path returned to locate and
display the image.

--
HTH
--
Jerry Spivey
MCT, MCSE, MCSD
Senior SQL Instructor - Consultant
ARIS Corporation  Bellevue, WA
(Please reply to the newsgroup only, not by email.)

Quote:> I'm in the process of trying to design a database with SQL Server 6.5 (to
be
> upgraded to 7.0 by December) which will contain photos of each employee in
> the table.  I've read somewhere that I shouldn't have a column where I
store
> the employee's photo along with the rest of his record.  That I should
link
> to it (in another table??).

> I know this newsgroup is frequented by some hefty experts so hopefully
some
> of you will be able to guide me on the right track.  This SQL Server will
be
> connected to the Internet if that makes a difference in my scenario.  If I
> shouldn't put my images in the same table as the rest of my employee
> information, what are the design strategies I should take for the images?

> I am new to SQL Server 6.5, so feel free to also explain the steps needed
to
> accomplish something.  Thus far, this has been a beneficial newsgroup and
I
> thank you all in advance for your assistance.

> Best,
> Mike

I disagree. I use SQL Server 7.0 to store and display images with no
noticeable degradation in performance whatsoever (check out
www.weezgot.com - all images stored in SQL 7). Mike, probably what most
people do is to do something naive like a select * from employee where
emp_id = 10, thus returning the image data with the row and thus the select
seems slow. What you want to do is always use a select statement such as
select lastname, firstname from employee where emp_id = 10 when you don't
want specifically want the picture data. The
image data is not stored in the row, only a 16 byte pointer to the image
data. If you are thinking about doing some type of load balancing, such as
having all the image data come from a different drive than you would want to
move the picture data into a different table. This is what is done on the
www.weezgot.com website. The item information is in one table and the
picture data is in another so that the picture data can be moved to an
entirely different RAID array if performance degrades (at this point that is
not being done).

Stephen Johnson
WeezGot.com Inc.

Stephen:

I checked out your site.  It is very fast in pulling up those images.  If
you have at least one picture per each of your subjects (I noticed you had
over 3000 trading cards), then you're doing something with more pictures
than I'd be working with.  I'll play with SQL Server some more.  It's not
hard to use, but the designing of the database does require some thinking
out beforehand.  Thanks for the help.

Best,
Mike

Mike,
    Not all of the images have photos. There are currently 1,523 pictures
with storage space usage of 40,773 K. Just some numbers for you. I use an
ISAPI.dll which I have written in C++ do do all of the generating of pages.
Just a little more information for you.

Hope it helps,
Stephen

Quote:> Stephen:

> I checked out your site.  It is very fast in pulling up those images.  If
> you have at least one picture per each of your subjects (I noticed you had
> over 3000 trading cards), then you're doing something with more pictures
> than I'd be working with.  I'll play with SQL Server some more.  It's not
> hard to use, but the designing of the database does require some thinking
> out beforehand.  Thanks for the help.

> Best,
> Mike

You can also design it with automatically generated keys in say, an employee
table that just has really basic info in it.
Then other tables that have foreign keys pointing to the employee number or
an automatically generated key that only the server uses.
parent...child pattern.

Quote:> I'm in the process of trying to design a database with SQL Server 6.5 (to
be
> upgraded to 7.0 by December) which will contain photos of each employee in
> the table.  I've read somewhere that I shouldn't have a column where I
store
> the employee's photo along with the rest of his record.  That I should
link
> to it (in another table??).

> I know this newsgroup is frequented by some hefty experts so hopefully
some
> of you will be able to guide me on the right track.  This SQL Server will
be
> connected to the Internet if that makes a difference in my scenario.  If I
> shouldn't put my images in the same table as the rest of my employee
> information, what are the design strategies I should take for the images?

> I am new to SQL Server 6.5, so feel free to also explain the steps needed
to
> accomplish something.  Thus far, this has been a beneficial newsgroup and
I
> thank you all in advance for your assistance.

> Best,
> Mike