where to store documents ?

[Issam]

Hi,

i am working on my little healthcare Application using WPF and Telerik openaccess .and i want to add a document manager feature, so basically each patient record can be associated with different documents types : images/docx files/pdf .... etc .

now i am facing the classic choice of storing these files in the os files system or directly in the database (Firebird) :

the second option is really tempting to me and present several advantages:
- having a compact single database file .
- the developpement process is much easier and make more sense to me , i mean manipulating navigation properties of the patient entity and dont worry about files saving on the disk is a big plus
- security access of the files is associated with the database security mechanism


THE main drawback to me would be the performances:
and since is my first real project with openaccess and ORM in general, i am wondering if it's a good .. or let's say not a very bad idea .to store everything in the database ?

if not, what could be the best practices and considerations to handle this case

thanks and good day

[Kaloyan Nikolov]

Hi Issam,

As you already pointed out there are many good reason why you would store documents in the DB such as security, simplicity, transactions, etc. A lot of systems are using this approach like SharePoint for example. There are no any Telerik Data Access specifics that you should consider when making your architectural decision. 

However I would not suggest you to place those files in a database if you expect to have lots of documents as this could drastically increase the size of the DB file. Bigger size of the DB leads to a performance decrease as you already mentioned. Another think you should consider is how those files should be opened for view/edit. If they are stored on the file system would be a bit easier because all you need to store in the DB is the location of the file and then the default program can open the file. If it is in the DB you should extract it in a temp file, open it and then probably delete it. 
Lots of system use this approach even some document management systems. It is OK but you should really estimate how many and how big documents you will have. 

If you plan to store a few small documents probably the DB is a good chose. 

To cope with the performance degradation you can think for horizontal split of the DBs and have dedicated DBs for documents, even multiple of them. 


I would suggest you to abstract the logic behind the document management in a Service or Manager class so that whatever you select as an approach you can easily change it later if you see it doesn't work well. 

I hope this helps. 

Regards,
Kaloyan Nikolov
Telerik

 

 

[Issam]

Hi Kaloyan,
suppose this real case :

i have a patient entity and a navigation property named docs  witch contains the patient related docs and images .
and somewhere in my UI a textblock binded to Docs.Count to display the total number of the patient's documents.

Now how  the underlying blobs loading is handled,

is there a tweak or a configuration call that i have to do to tell to Openaccess to load all the blobs explicitly ,

or everything is loaded automatically when the parent the entity is loaded ?



anyway from what you said , it seems that it's not a very bad idea, so i have to try it :)

thanks again !

[Kaloyan Nikolov]

Hi Issam,

Most probably you will organize those entities in 2 different tables, something like Patient and PatientDocument in a one-to-many relationship as beside the document you would store additionally some metadata like the title, date, etc. With such setup the Patient.Docs navigational property will be loaded from the DB with the first access to it unless you specify a fetch strategy which loads the documents along with the Patient record.  

Alternately you can have 3 tables: Patient, PatientDocMetadata and PatientDoc. The navigation property will load the metadata, which will be fast operation but the actual documents will not be loaded unless one of them is accessed. 

The rest is architecture design which you should do considering all of the constraints :)


Regards,
Kaloyan Nikolov
Telerik