What are my database options?

What are my database options?

Several database-connection drivers are included with Base. Some provide read-only data access, while others provide full read-write data access. For example, the included read-only "Text" drivers provide access to existing data stored in tabular-spreadsheets (.ods/.xls), delimited text files (.csv), or popular address-book formats. The bundled "dBase" driver provides full read-write access to flat-file "dBase" tables, to include table creation and rudimentary queries through Base (requires write-privileges to the database folder); this dBase driver provides flat-file database functionality comparable to MS Works. But Base goes much farther. Base also supports standard JDBC or ODBC driver access to all major database engines, as well as native-SDBC drivers written specifically for *Office (see: PostgreSQL, MySQL, etc.). The bundled HSQL relational database engine enjoys tight integration with Base, but HSQLDB provides an external JDBC driver option which increases reliability and provides multi-user database access (see 'split HSQL database' below). The bundled HSQLDB engine is often sufficient, but HSQLDB 2.x takes Base to new levels with all the latest desktop database features and functions. Base can also be connected to full-scale engines, but be aware that these RDBMS's generally require an alternate database manager for DDL tasks and general setup -- although it is possible to send the necessary low-level commands using the Tools > SQL console in Base.

• With so many options, it can be difficult to choose the best path for a Base project. In general, if you're building a database from scratch using Base, the bundled HSQLDB engine is the 95% solution. Very few Base users will ever exceed the features and functions provided by this bundled engine (HSQLDB 1.8). But if you outgrow the bundled version, it's a relatively trivial matter to upgrade to HSQLDB 2.x which is fully sufficient (99% solution) with Base -- because it's virtually impossible to exceed the capabilities of this full-featured SQL engine within the context of a Base desktop/LAN project. HSQLDB 2.x features include: modern SQL support, multi-user security/optimizations, advanced data-types, stored procedures, ACID reliability, encryption, hot-backups, billion record databases, and much more. So it's probably best to begin with HSQLDB 2.x whenever possible.

• Of course, Base can also be used to access MySQL, PostgreSQL, and other popular full-scale RDBMS solutions. This is a critical feature because it provides access to corporate data, or other existing databases. Otherwise, these full-scale RDBMS solutions are overkill for new projects with Base due to more complex setup and daily maintenance requirements, while adding little -- if anything -- to a Base-centric project.

• At the other end of the spectrum are those who simply maintain their data in tabular format using a spreadsheet. No problem. Base connects seamlessly to data in popular spreadsheet or delimited-text formats. However, the data is read-only through Base and a spreadsheet can become unmanageable beyond a few hundred rows of data. The dBase driver takes it to the next level with read-write access to one or more flat-file tables, which can be created through Base (ensure write-privileges to the dBase folder/files). These basic drivers also supports some rudimentary query capabilities through Base, thereby providing a flat-file database capability quite similar to MS Works. This may be sufficient for many Base users, particularly as SubForm-links may be used to create ad-hoc relationships between tables. But unless you're working in a Java-restricted environment, the bundled HSQLDB driver can also enhance flat-file data access. Of course, HSQLDB can be used to create both flat-file and relational database tables. But did you know that HSQLDB can also be used to establish read/write connectivity with delimited-text files (such as CSV)? So HSQLDB becomes the best choice with Base even when you're simply connecting to tabular data (CSV delimited data) such as a spreadsheet, since this connection method allows both read & write capabilities. So HSQLDB subsumes the function of the Text and dBase drivers with Base (while making child's-play of SQLite).