Wednesday, May 12, 2010

Limitations for databases that will affect your software

There are a large number of things to look in a database upon selection. Here are some that have caused me problems:

  • Column name size. e.g  firebird it is 31 
  • Alter table e.g. SQLite does not allow drop column
  • Authentication e.g. ScimoreDB 3.0 does not have db password protection 
  • Max DB Size : e.g. SQLite can only handle databases of 2TB 
  • Embeddability: e.g. MSSqlServer cannot be packaged as a part of your application (SQL Compact can be ... but it has its own HUGE limitations) . MySQL is a 60MB+ database. 
  • Licence : MySQL is GPL and not free for commercial use. Per installation cost as of 5/12/2010 is about $500
  • Reclaim Disk Space: Deleting data from a firebird does not shirk the database size. It is stored in the firebird free space and reused but cannot be reclaimed without backup/restore. 
  • Performance : test it out ... you never know ... depends on the database as much as on your configuration / your application code / the application interface. 
Note: all databases mentioned here are great for a there own things e.g: Scimore has the best distributed architecture I have seen , SQLite is the best "Embedded only" database there is with an awesome .NET provider, Firebird has great entity framework support and so on.