Post reply

How much it is feasible to store the photograph of Visitor in table?

  • October 7, 2008 at 4:21 am

    #191893

    Hi All,

    I am using SQL-2005 database.

    I came across the requirement which demands to store the photograph of visitor or contract employees in database table. The strength of the visitor or contract employees would be around 1500 per month. My question is

    1.Will it be fesible to store the photogrph of each visitor or contract employees in database in terms of database size

    or

    2.It would be better to keep all the photographs as .jpg at different location and keep the link of the same in table(

    or

    3.Is there any other solution, which will fulfill this requirement.

    Thanks in Advance.

  • October 7, 2008 at 5:43 am

    #881564

    Storing photo's in your database will certainly increase the database size. This will have impact on your backups and recovery. But this option will give you enhancements about integrity, because everythis is handled by the database.

    It is also possible to store as files on your disk and store only a link in the database. This will reduce the size of the database but increase the problems with integrity between the database and the filesystem.

    You (or your business) have to decide what the best approach is for your situation.

    In SQL-2008 you have the new FILESTREAM feature. This will let you store files on the disk-system instead of in the database, but the database will handle these files, thus ensuring the integrity.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 7, 2008 at 5:46 am

    #881566

    1) Yes, it is feasible to store the images in the database. Disk space is cheap these days.

    2) You can do this, and some will argue it is better to store them on the file system rather than in the database. Here are a couple of things to consider:

    a) Speed - getting an image from the file system will usually be faster (assuming you put the files in an appropriate place and don't have to copy them around)

    b) Backups and restores - if you put the images on the file system you need to back up the file system with the images every time you do a database backup and manually manage restoring them together to ensure you do not orphan images or end up with bad links to them

    c) Portability - it is typically much easier to create a development environment if you are not using the file system to store data. This is for some of the same reasons as the backups.

    Finally, if you move to SQL 2008, there is a FileStream data type that is the best of both worlds - because your issue is a pretty common one. If you have a lot of data access to the images, this is the best direction to look into.

  • October 7, 2008 at 6:04 am

    #881578

    My preference is to store the JPG.s in a directory and store the file name in the server for such a requirement. The reason for this is that it...

    1. Starts out as a JPG anyway

    2. Can easily be resized to meet view specs or enhanced for clarity by just about anyone or any program capable of doing so.

    3. Once the JPG exists in it's proper format, it's usually pretty static. I'd prefer not to have to back them up with the rest of the database. If the files are stored in a directory, the ARCHIVE bit keeps good track of any changes that need to be backed up.

    Yeah... it does mean one extra trip to the server... but I've seen extra trips to the server for a lot worse reasons. 🙂

    Just to be fair, I've found that the general guidline is that if the JPG's are of reasonable small size, it's probably OK to store them in the database because it does make things a little bit faster. Obviously, I don't follow that guidline for the reasons previously stated.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 7, 2008 at 7:16 am

    #881617

    I've gone both ways on this one. In one case, the pictures were the central data for the database, and it made more sense to store them in the database. In another, a "file pointer" in the database made more sense.

    With the database of pictures, database security and rules allowed for control of who could see which pictures. Could even audit that access through a trace. Can't really do that if they're all in a folder and can be accesses without going through the database.

    Keeping them in the database can make for big backups. You can manage this somewhat by setting up files for different table types, and controlling backups on those.

    The biggest drawback to keeping them in the database is, however, speed. SQL doesn't store or serve up pictures as fast as NTFS does. Not even close. That's one of the major reasons for the filestream data type in SQL 2008, per Microsoft.

    So, if you don't need to use the database to control access to the pictures, I'd keep them outside the database and just keep a table of file names. It's just easier that way.

    Or switch to SQL 2008 and get the best of both worlds.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 8, 2008 at 6:49 am

    #882224

    Hi All

    Thanks a lot. I will now just check the same.

  • October 9, 2008 at 3:44 am

    #882772

    Like many others, I feel queasy about the overhead of storing files in the database. Whilst our building access control system (from a third party) stores and retrieves c.7000 pass card photos in SQL Server 2000 with no problem at all, these are small files, rarely updated. Our Quality system stores PDFs and DOCs in the (2005) database, and we will see soon how well it copes. But on the other hand, there is the appeal of data being inaccessible outside the DBMS, surely something we would all prefer. It concerns me more that a file referenced from a database record may be changed or deleted without the database knowing about it.

    If it secures the managed files from outside intervention, FILESTREAM may well be the solution. At our rate of change we may be adopting 2008 in about 2012...

  • October 9, 2008 at 7:57 am

    #882901

    [...]With the database of pictures, database security and rules allowed for control of who could see which pictures. Could even audit that access through a trace. Can't really do that if they're all in a folder and can be accesses without going through the database.[...]

    No one should have direct access to the directory anyway. File security should be handled by the middle layer.

    Also, I could be wrong, but I heard that if you have files in your tables, SQL may use up large chunks of your data cache. I'd rather have SQL cache tables, not pictures.

    I think the client should connect to the middle tier which then makes a request to the back-end DB to get a picture, then the middle tier serves up the file to the client. If it's a web client, the DB can generate guids to represent the picture and the asp web page translates those guids into pictures andthe guids expire after some set timeout period.

  • October 10, 2008 at 12:22 pm

    #883589

    My application has a relatively small number of records (about 15,000). Of these, about 3,000 have a picture. These are jpeg files, about 50 kBytes each.

    Access speed is no problem.

    I have stored these as a text type. This allowed Crystal Reports v. 8.5 to access the database directly to print the picture from a simple SQL query.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply