Best database to store and retrieve Word and Excel documents

Question

My current project is to create a barcode system (for lack of a better word) that is linked to a database (was not given more information or directions). My task is to have a barcode scanner interpret a barcode, query a database that stores MS Word and Excel documents (.docx/.xlsx), then automatically retrieve and display the document. I have not received much direction other than when a barcode is scanned, to have the corresponding document to be retreived and displayed automatically. There is currently no database at our location and we store all of our information in Excel spreadsheets.

The process that I am attempting to automate is to remove the "human element" from opening the correct document(checklists and reports) as well as saving the document with the proper naming format and location (excel VBA script).

There is no reason why this should be impossible, but I am not sure which (R)DBMS to use. We are not looking to spend extreme money (no oracle 11g or 12c), but we are essentially starting from scratch and our spreadsheets are spread over the entire networked drive. I know there are options like IBM DB2 or MySQL or MS SQL Server or MongoDB...but I am not sure which one would be the best "bang for my buck" and give the least amount of headaches.

Answer 1

Personally since you don't have a relation beyond barcode --> document I don't think a relational database is the best fit.

If your requirements are really as simple as:

1. Find a document
2. Serve document to user

Then any Key/Value store should work. This wikipedia article talks about some of the theory for Document Databases as well as some pro/cons vs Relational Databases.

You will need to write some sort of interface/application to translate the barcode reader output into a query for the document in the database you choose. As such you might not even need to use a database at all. You could just have the application layer find the documents in the network drive...

Since I was pressed for a specific recommendation. I would look at MongoDB. It should be cheap to run, will fit you needs, and appears to have broad support. Here is a link to a blog article discussing storing files/binaries in MongoDB.

Fair warning: I don't have any direct experience with MongoDB, and only basic familiarity with document databases in general. As such my recommendation of MongoDB is based on hearsay, and general impressions.

Answer 2

Another aspect that the other answers are not addressing is your (and your group's) expertise. What you have described is a very generic RDBMS workload. Most of them are capable of storing the binary data as well, up to limits that will vary between different implementations.

The best solution for you is the one that will be the easiest for you to run and maintain, long term, in your environment. If someone has expertise in a particular database, that will probably be the best one to choose, unless it just won't work.

If you have no experience and no one to run it, your best bet may be to use a well supported open source database that is relatively simple. This probably means PostgreSQL or MySQL. (MongoDB is notoriously complex to deploy and keep running.) If you are a Microsoft Shop, SQL Server could also work.

Once you have one, make sure you take backups (and test them by restoring them somewhere else!). There will be tons more to learn and tune, but only once you have the pain point.

Answer 3

MySQL Perspective

If you wish to load Documents .docx and .xlsx files into a MySQL Database, you will have to use the LOAD_FILE() function. Here is what the LOAD_FILE() Documentation says:

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

Here is the example of its usage

mysql> UPDATE t
       SET blob_col=LOAD_FILE('/tmp/picture')
       WHERE id=1;

From here, you could load each file into a BLOB and store that BLOB in a table.

You would have to make sure the DB Server has SAN storage mounted. You would then specify the file with its fullpath.

Answer 4

Even more solutions / points to consider

In addition to the suggestions offered by fellow members of the community, I would like to also give you some further points for consideration, which are a number of important learning points that I have picked up over the years while working on similar projects that may have had very similar pitfalls or complexities to battle against!

Your initial database that features barcodes and contains all of the databases and spreadsheets, could be described as a Repository of information assets, described briefly using systematic metadata which would be a detailed list of the indices that follow prescribed systematic procedures using look-up files to complete a 13 digit unique number which would be translated by a computer using a barcode.

Information assets would include all sorts of electronic files, e.g. it could be a list of three live databases, or a log of quarter 3 project working documents, perhaps even individually labelled project files and all other electronic instances of data sets that should all be tagged and registered as information assets.

Every single entry should be systematically registered using the appopriate reference lists to describe the details of owner, purpose, type, system, risk, planned disposal data and other similar datasets that would be related or associated.

Updates or revised data sets would continue to be developed and coded identically with all other sets of the same source, however version control procedures would be followed to maintain accurate logs as described in the document storage policy and procedures manual.

A new information asset or registration of data set could be registered once it has been created or obtained, by identifying a barcode to describe the content/owner/subject/details of the dataset as the 13 digit code would be broken down to show how every code represents a set of referenced lists of variables that would be described as the metadata fields within the overall data.

A library of various national releases of datasets are referenced as collections of data or statutory collections with hyperlinks to the actual source. It is standard practice not to recreate copies of the same data unless edited, which should then be appropriately labelled to reflect new version that describes any related documents or reports that were produced based on edited datasets.

The languages used to maintain directories, indices, version controls and catalogues of many different datasets or electronic reports would resemble the architecture of the Intranet which uses HTML and CSS with additional features using javascript and php to provide dynamic web pages that would all be managed by a systems administrator, however the engine would be a complex network of various Microsoft SQL server, MySQL and VisualBasic based software applications being connected in ways that ensure full connectivity between systems should there be a significant loss of power, with added support of suppliers who may have control over certain functions of deployed systems. This is typical of a large organisation operating across many different platforms with multiple software suites not designed to be connected in any way that modern technology provides simple codes to achieve even across different environments.

I have also developed a completely different index of electronic files based on the XML schema to process large data files and register specific metadata entries on a central repository. However, every database was then restructured to follow the XML schema that then allowed every entry to be used as the underlying raw data that would be securely hosted on an intranet that would be easily retrieved by MySQL stored procedures and functions based on triggers that ran scripts based on various different sets of rules.

I've seen some brand new programs that are based on versions of php that were introduced for very specialised projects, including all of the types of projects you've mentioned, so without taking up any more of your time, let me leave you with a link to a website that probably gives you a whole suite of codes and functions that may deliver what you no longer need to create from scratch, with the added supporting documentation that is easy to understand yet very detailed indeed.

DOCTRINE (php) and Yama Schema

Perhaps, you would be interested to know about the Doctrine/Yama solution that is designed to handle complexity with simple classes, trees, labels and functions to make it fairly simple to see sensible models be easily built without much of the hard work you might need to put in to setting up and securing other systems.

• Doctrine ORM: Introduction to Models
• Doctrine ORM: YAML Schema Files

Answer 5

All you need is bar code to file

I would just store bar code and file path in XML.
When you load the application you read the XML into a dictionary.

Use a dictionary lookup get the file name and then just read the file from disk.

You you can use any database that will store a binary (they all do).
This is just a simple table.
It is kind of a pain to read and write binary.

It is is easier to read files from disk and makes backup and restore easier.

P.S. I write document management software for a living

Answer 6

Might wanna try simple file storage, just use the barcode as filename. You can do that on a webserver too if you need it remote. Otherwise FirebirdSQL can handle large amounts of BLOB-files and it's free. MongoDB has a mode GridFS for storing files, not sure how easy to use it is.