Full-Text search allows you to find records by similarity, which is what most users expect these days (thanks Google). Some moron will tell ya, "oh you can use Lucene or Sphinx". Too bad they don't really talk to databases eh? Ya, polling your database for modified records every minute is a great idea. Upon getting Lucene to sync with MySQL you will end up like Mel Gibson in this scene:
http://www.youtube.com/watch?v=UpOqkz86_lg#t=135s
And you need transactions and check constraints if you remotely care about the integrity of your data. If you don't, please continue to use MyISAM.
You need schemas (real ones, not MySQL ones) if you want a multi-tenant system that doesn't suck. If you only have one tenant, this doesn't apply. You can still do it with MySQL, but it provides much butt-hurt.
On the application server side, you need a comprehensive validation library, deeply-nested JSON support, support for table inheritance, and concurrency control in your ORM.
By deeply nested JSON support I mean being able to load JSON from a POST into your models and into your database in a couple lines of code, with validation. With hasOne, hasMany, and manyMany relationships. You should also be able to write 50 records or so, with relations of arbitrary nesting depth, very fast.
By support for table inheritance, I mean this: http://martinfowler.com/eaaCatalog/singleTableInheritance.html , and the other schemes. Ruby's ActiveRecord supports STI, and PHP's Doctrine supports single and multi. Yii's ActiveRecord sorta-kinda-almost supports STI, but requires some work.
Concurrency means that your UPDATE will fail if you try to update a record that someone has updated since you read it. Usually this means a query like this:
UPDATE mytable SET (field1='happy' and field2='monday') where id=59 and (field1='sad' and field2='sunday')
Unfortunately, this is not built in to Yii, which I have been using. Yii's validation library is actually really good, but it sucks at nested JSON and it doesn't do concurrency. It looks like Doctrine ORM does concurrency, but has no validation for some bizarre reason. I might have to switch to Ruby, or hell, even go back to ASP.Net
Have you looked at Oracle XE, it's free (as long as you can live within the limitations), and PLJSON which is an open source PLSQL libraray for dealing with JSON serverside in the DB. You can use "Oracle Text" for searching.
ReplyDeleteYou can eliminate a lot of the app server requirement by doing everything in the database.
You can still use app servers like glassfish, tomcat etc. if you need any j2ee support.
Just throwing this out there as an option worth investigating, it's my preference of choice for systems that don't have a high number of concurrent users, and for ones that do there's Oracle SE, EE & RAC to upgrade to (if they don't have it already and of course have the money)