redesign with SQL, NoSQL or EAV
I'm looking to do a substantial upgrade(redesign) of an applications that...
- has code and database on the same managed server
- data has lots of bins(100) that have lots items(100-500)
- items have additional fields, some required only for small subset (few bins)
- is multilingual, (has separate tables for name values)
- relatively low amt. of items (in 10's of thousands not millions for now)
- 99% reads are done from top down, i.e. looking for a list of items in a bin, not looking for all items with a said tag
- more reads than writes
- has multiple levels of access (full admin, multiple bins, one bin, one item, only items matching criteria)
The reason I'm looking to change...
- add multiple item types, ideally dynamically by setting up templates through web interface (EAV?)
- improve performance. Multilingual queries with fallback values are costly. Targeting database for the type of reads that are most common should speed up listing of items
- considering a move to RESTful apis, could use benefits of data store
- weak/rigid security implementation. Access rights are handled by application based on 3 cases. I would like to add more user types, without having to code each user case into the
- add undo states. Currently logging actions only, finding previous values is difficult
- move to cloud/paas to make horizontal and vertical scaling easier and cheaper
I'm wondering if EAV on SQL, EAV on NoSQL or a DocumentStore noSQL can help to solve some of the issues. I know that the SQL could be tuned to improve the performance and the schema can be extended to do class table inheritance when other classes are needed.
SQL has the advantage of having schema, but the ability to template the item types only work if there is no set schema. Whether EAV or NOSQL is used, the application has to carry the burden of data integrity. The advantage is that I can (ideally) code for a view/model templating system that can handle any data without further changes to code. I can add multiple textboxes, radio, select, checkbox, image etc. easily, and changes to the app are only needed when a new data type needs to be handled that has unique functionality. The SQL-way, I have to update the schema and then the application, given that the data that is being stored is changing frequently, I would rather have the ability to use schemales data.
The core of the database is really handling items, listing creating and updating items. My thinking is that if the data item that is pulled from the database is about 90% either a single item or all item within 2-3 bins, a document store like MongoDB seems like it would work well, since I'm calling either a bin or a item inside the bin in most cases.
I don't think I'm holding enough information though to require horizontal scaling just yet, so things like sharding are not required, but redundancy would be handy, and if the cost of implementing that is low on a cloud hosted db, it's worth utilizing.
The multilingual fallback (if a value is not defined in one language, pull from another) functionality doesn't seem to be addressed by EAV or noSQL solutions. This kind of querying seems to be the biggest bottleneck in my SQL database, but the other solutions would rely on the application to handle this functionality anyway.
So is it worthwhile to switch to switch for these reasons, or is this just a case of lust for a shiny new technology?