A good database system for lightweight schema-flexible problem

Question

I'm looking for a DB system to use for a small project. My ideal DB would have the following features/requirements:

• flexible schemas: I have tuples that might have missing attributes, and others that might have special attributes not shared by the entire database
• convenient front-end: This DB would be part of a (most likely web-based) front-end where a non-tech-savvy user would enter data, ask queries and so on. So appropriate plugins with php would be handy.
• query expressivity: I have a reverse-scale problem - the database will never get terribly large (think of something like 50 new tuples each year) so I don't need something very fancy. I just want a system that admits fairly sophisticated querying (which is why a spreadsheet won't do)

I've looked into the usual NoSQL systems like MongoDB and CouchDB, and while they definitely could work, they seem like overkill for my relatively small problem. I was wondering if there was something much simpler that I could use.

Answer 1

I am a fan of SQLite. SQLite supports many of the basic features of DBMS's like MySQL such as querying with standard SQL, but runs as a single dll included into most programs. The database itself is stored as a single file, and with the amount of data you appear to want to put in it would probably be pretty insignificant in size. SQLite does work with PHP, and you can find a tutorial for getting started with it Here. Enjoy!

Answer 2

From your description, it appears a doc store or a column store would work well for you. As most nosql variants are for "big data", most of the nosql options will probably be overkill for what you're wanting to do.

How big is your dataset now? If it's fairly small (matter of tens of gigs), then you would probably best served by an in-memory database. That'll help with query execution, and you'd only need one node/server.

That said, something like mysql may be ok. Since you mentioned just small amounts of data, that's what's drawing me to simplicity.

You can then use open-source tools such as Pentaho or Jaspersoft to extract out the data via reports. They're very easy to setup and use.

Thanks,

Jeff