Best Database to Store and Retrieve Word and Excel Documents

database-recommendation

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.

Best Answer

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:

Find a document
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.

Related Solutions

Best db and file system for high volume, high qps

Virtually any database server can meet your needs, whether it is MySQL, Microsoft SQL Server, Oracle, etc. Performance with all the rdbms' vary a lot based on the following criteria

I/O Performance of the disk (get good, fast, RAID'd disks over high throughput connections)
Performance and Amount of RAM (good and fast RAM, and lots of it)
File System Overhead (use a modern fs designed for databases. Ext4 works fine, but there's OCFS2 that is Oracle's file system, built for databases, or even Oracle's RAW storage where there isn't even a file system in the OS-sense... the database manages the disk entirely.)
Properly Indexed table structure (quick access to data depends upon your data being indexed correctly, where "correct" means most efficient for all your needs.)
Properly Optimized Queries... usually goes hand-in-hand with the correct indexes; the database can try to figure out the best optimization for your query, but it can only do so with correct indexes and statistics. Better yet, fine tune your queries so that the database doesn't have to do a lot of optimization on its own, and you'll be faster and happier for it.
I/O Performance of the network - if you are transferring a lot of data in or out, network performance will matter (otherwise the database has to wait when sending data across the wire).

A few things that really don't matter very much (as far as performance):

Your operating system. Windows, Mac OSX, Linux, and anything else that's modern will do just fine as the operating system layer. Most of your work is done in the database anyway, with occasional tasks needing done in the OS. Pick an OS that will simplify your life (and not complicate it), but modern databases are performant on most OSes.
Your database engine. Yes, there are critical differences between MySQL and Oracle and MS SQL, etc. Does it really matter? If there is a particular feature you must have, then by all means, go for it, but ultimately, all will be sufficient with the correctly configured hardware and tables.

Really, pick a database, an OS, a file system, and build your app. With good hardware and good table design (with indexes and optimization), things should go swimmingly.

All that said, here's what I'd pick (only because I'm most familiar with this type of configuration):

RHEL / OEL / Ubuntu (OS)
Oracle Standard One (or Standard, or EE, depending on your needs) (DB)
Ext4 Journaled File System (FS)
2 2xCore Processors (>2.5GHz)
8 GiB Memory (something around 1066 or faster MHz)
1 TiB HDD (SATA, with >=7200rpms)
At least gigabit ethernet

Swap out the OS, File system, and DB as desired. Your machine should scream and be just fine for a good number of databases, and is probably a bit overkill. Best thing, though, is that you can always add memory and disk space and upgrade your processors if you need more. If necessary, move to a new box, or add boxes (if your database supports it). (Even better: run your environment as a Virtual Machine/Cluster. Then moving to another physical machine is child's play.)

Best database system to store and compare date ranges

PostgreSQL has the datatypes daterange, tsrange (timestamp range) and tstzrange (time stamp with time zone range), along with a number of operators and functions to process, cast and extract information from them.

It also has EXCLUSION constraints, which can be used to ensure no two rows have overlapping ranges. E.g.

CREATE TABLE reservation (
  room_id int not null,
  customer_id int not null,
  during tsrange not null,
  EXCLUDE USING gist (room_id, during WITH &&)
);

The above table would not allow the same room having overlapping reservations.