Database Model To Manage Documents

I need to build a tables related to manage documents such as jpg,doc,msg,pdf using a sql server 2008 . As i know sql server support .jpg images, so my question is if it's possible

Solution 1:

You can store almost any file type in an sql server table...if you do, you will almost certainly regret it.

Store a meta-data / a pointer to the file in your database instead, and store the files themselves on a disk directly where they belong.

Your database size - and thus hardware required to run it - will grow very rapidly, so you will be incurring large costs that you do not need to incur.

Solution 2:

Use Filestream

https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

I know that a link-only answer is not an answer but I can't believe no one has mentioned it yet

Solution 3:

The proper database design pattern is not to save Files into DBMS. You should develop a kind of File Manager Subsystem to manage your files for all of your projects.

File Manager Subsystem This subsystem should be Reusable, Extendable, Secure and etc. All your projects that want to save Files, can use this subsystem. Files can be saved in every where such as Local Hard, Network Drive, External Drives, Clouds and etc. So this subsystem should be design to support all kind of requests.

(you can improve the mentioned subsystem by adding a lot of features to it. for example checking duplicate files,...)

This subsystem, should generate a Unique Key for each file. After uploading and saving the files, the subsystem should generate that key.

Now, you can use this Unique Key to save in database (instead of file). Every time if you want to get the file, you can get the Unique Key from database and request to get file from the subsystem by unique key.