Lotus Notes is not really comparable to RDBMS's, or so the Lotus Notes group at work tells me. The RDBMS is much more efficient for large structures, but notes works well for small scale rapid development. IBM is rewriting Notes to start using DB2 as its back end because of the issues with the Notes SAM structures in large scale environments.

Where I work, our real-time production database is roughly 8TB in size. We support roughly 8,000 concurrent users in a real-time business environment on multiple platforms, processing roughly 1,200,000 database transactions per hour. We're not using Firebird at work, but the principles of ISAM based RDBMS design don't vary a lot - only the specifics for particular implementations.

In my experience, we break relational databases apart so that the real-time processes are not impacted by ad-hoc querying for decision support, or for security reasons. Each of our databases has several hundred tables (1,200 is the figure that comes to mind), with anywhere from 1 to several million rows. Busy tables will be broken into a "current" and "warehouse" table, with a nightly archive job that moves data from the current to the warehouse as it ages. Where data must be propagated between databases in a timely manner, we use a MOM product.

Indexing is critical for your database app. Design compact indeces and minimize I/O. Keep relationships simple, and the processes to maintain them simple as well. Avoid duplicated work. Never delete rows (hot button ... oops), except as the cleanup after a successful archival process..

[Non-text portions of this message have been removed]