Thursday, November 19, 2009

Choosing database for a POS

While developing an open source product its important to consider a matching database engine. Fortunately there are a number of stable database system already tested and recognized both web and Java applications. For Restaurants we required
  1. A Public domain license so that we can distribute DB unconditionally with software.
  2. Concurrent access - multiple clients may access 
  3. Embedded mode preferred ( to run in single work station)
  4. Database joining, subquery and view especially for reports
  5. Fast DB transaction but transactions my not be huge
  6. Friendly with Java (since we already selected java)

Following matix shows a comparison among different DB engines. We have eventually selected Apache Derby because it fulfills most of the features. In addition it can be embedded as well as its license is more flexible than MySQL.


Apache Derby 10    MySQL 5
PostgreSQL 8



SQLite




Database Connections


Multiple


Multiple


Multiple


Multiple



Concurrent Access to Multiple Databases



















Embedded Database










x




x






Unicode
Support






















Replication Support



















License


Apache (BSD)


        GPL


BSD


Public Domain




Specifications


















SQL 99


       √


         x             √



                   x


SQL 92
















  x



Relational Database Features


















Sequences/A
uto-increment Column
























x





User Defined Functions






















x




Update-capable Views























x





Referential Integrity























x





Triggers



Statement / Row Level


Before / After


Nesting


Compound























x

























X











x












x

























x





SUBQUERY




















               x       




OUTER JOIN





















                 x





Name Length Limit


128



64


64



255


Delimited Identifiers




























Stored Procedures



























Technorati / QK5RE4N7N9S9