1

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 to upload other kind of files into a db.

This is an example of the table (could be redefined if it's needed).

Document : document_id int(10)
           name        varchar(10)
           type        image (doesnt know how it might works)

Those are the initial values for a table, but i dont know how to make it useful for any type.

pd: do i need to assign a directory to save this documents into the server?

alex
  • 113
  • 2
  • 14
  • 1
    look at this https://stackoverflow.com/questions/15356154/file-stream-vs-local-save-in-sql-server – Horaciux Dec 29 '17 at 12:45
  • Don't use the `image` datatype. It has been deprecated for sometime (since 2008 if i recall correctly). Instead use `varbinary`. – Thom A Dec 29 '17 at 12:45
  • 4
    The safer way is to store the files on a file server and the path in the db. – Dan Bracuk Dec 29 '17 at 12:46

3 Answers3

3

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.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
  • there are around 10 users that actually uses this system and around 100 transaction on the entire years, around 100-1000 documents related to jpg,doc,msg or pdf. – alex Dec 29 '17 at 12:56
  • So it will probably work, and may even work for quite a while even if your traffic and needs pick up - but I still don't think its a great solution; it will work until it doesn't anymore. – E.J. Brennan Dec 29 '17 at 13:16
  • I have to agree with E.J. This is a what I call a TBS (Taco Bell Solution)... seems like a good idea, but seldom is. – John Cappelletti Dec 29 '17 at 13:26
  • the solution is as a local project throught intranet(anyone can see the documents if it's connected in a shared folder) and those are the requirements i received (i agree this a taco bell solution too) – alex Dec 29 '17 at 13:34
  • If the requirement is to just store documents and nothing else then there seems to be little value in rolling our own Sharepoint. But if the application mixes documents with other structured data, that is a different matter. One consideration is backup and recovery requirements. It's a lot quicker and easier to restore a database than it is to restore a database and a filesystem to a consistent point. – APC Jan 02 '18 at 05:47
1

Use Filestream

https://learn.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

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • 1
    Link only or not, this is the best solution: it has all the advantages of integrated database management (especially backup and recovery) while dealing with people's (seemingly philosophical) objection to storing files in a databases. – APC Jan 02 '18 at 05:52
1

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.

Gholamali Irani
  • 4,391
  • 6
  • 28
  • 59