Database Argument against embedding documents...

I'm trying to make an argument against my client's desire to embed documents into a database as opposed to having links to files/document/spreadsheets/images, etc. in a related table - which is how I designed it. They had an IT person on staff who thinks embedding these files makes more sense.

I explained bloating, unnecessary network traffic, files becoming static instead of dynamic (they said they'll delete the files once embedded), etc.

Right now the back-end is an Access db -- but migrating to another back-end is not out of the question.

I have never even considered embedding files into a database as I was trained that it's not a good idea. Can you provide me some solid arguments or recommend a db that would handle this??

Thanks Experts!

Eileen
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
The first two things are that the documents can Only be retrieved thru the database interface and it can massively increase the database size which affects how long it takes to do database operations.  

Putting documents in the database loses all the meta information like 'Open With...' that the operating system would normally provide.  The documents would have to be extracted from the database and put in a file for the normal programs to open them in any case.  Word or Excel can't open a file while it is stored in the database.
0
TheAvengerCommented:
I think the embedding option is not that bad at all, especially with the current technologies. What I like about it is that you have everything together: data and files. If you only have a reference, you need to keep file system paths correct and make special backups. Note that systems like SharePoint save everything in the database.

As you wish arguments for saving the files separately, I would say that the major one is the database size. If there are a lot of records and these are often changed, it will make the database huge, fragment the table and generally slow the operations. You can however fix almost all of those problems with clever design, so they are not absolute. Another question is what DB system you use. I always see Access as a semi-professional system, so if you want to put attachments, etc. I would suggest moving to SQL Server (maybe the Express version which is free).
0
TheAvengerCommented:
I like the arguments of DaveBaldwin. However note that a good professional can beat them all. E.g. "we want everything to be integrated in our application and not allow users to access the files outside of it" or "if the files are on the file system users can move them without the system being informed". You should be pretty careful when approaching the matter and also consider it once again for yourself.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dave BaldwinFixer of ProblemsCommented:
@Avenger makes some good points for a 'closed' system that has software support for those operations built-in.  But in the general case where the users will be using standard desktop software, someone has to provide support that both puts the files in the database and then extracts them back out to a file for 'normal' use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
They have PDF's, Spreadsheets, Word Docs, files of various image formats, Power Point Presentations, and well as  txt and csv files.

They have 3 basic folders containing various sub-folders representing clients, pieces of equipment and or various installations.

I designed the application to permit the "attaching" of all files pertaining to each. I wrote functions to locate files by search strings to simplify the initial "attachment" process.

Subsequent to them linking all the files - the new standardized procedures will be to use the application to generate these various documents/files  instead of having each user use their own preferred method.

So- following implementation there will be no need for the creation of all of these various file formats and the reports will be generated using  a standardized methodology.

I probably should have mentioned this earlier - not sure it changes anyone's opinion on the matter... Eileen
0
TheAvengerCommented:
I think you personally have some good argumentation - the one you just wrote. Add to that the arguments of DaveBaldwin and as a good lawyer take some of mine and show your customers that you know the other side and you know what you are talking about. Thus you will show them that you respect what they want to say, understand it but still consider your solution better because of that and that. I think you have pretty good chances. Good luck!
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks a lot guys. You gave me arguments for both sides and will make me sound smarter :)

It was educational for me to learn the various issues. So, thanks again.

Eileen
0
Dave BaldwinFixer of ProblemsCommented:
You're welcome, thanks for the points.
0
PortletPaulfreelancerCommented:
Mmmm, many good points for both points of view - and I know I'm a bit late

but for Access in particular there is the issue of maximum file size to consider e.g.
http://office.microsoft.com/en-au/access-help/access-2010-specifications-HA010341462.aspx - I'd consider looking at some growth predictions based on documents inside Access - this may help make the decision.

btw: I encounter this issue frequently (but for Oracle & SQL Server) as I deal with apps that allow attachments which can (optionally) be held as blobs in the dbms or as files (by default). Here one of the strongest arguments for documents in the dbms is 'backup and restore'. With all documents in the dbms it is far easier to ensure the integrity of a backup/restore; with documents as files it is important to co-ordinate backup points so that for any given dbms restore you are able to also identify and use the relevant file restore point.

In most cases my clients (enterprise level) keep the attached documents as files and use cheap SAN for storage; secured in such a way that only the application can access the relevant folders. Usually this decision is also influenced by cost (as dbms storage often has a higher hardware spec. and/or dbms licensing is affected) - but this may not be at issue for you.

One argument in favour of documents in the dbms is security/reliability
If documents are in the file system - users may gain inappropriate access to some documents and/or in the worst case have the ability to delete the files (and if deleted your systems reliability is compromised). With docs in the dbms then security is simplified.

So if presenting for/against information I suggest you cover dbms size (esp. max size), backup/restore and security as topics.
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks Paul.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.