Database support is a broad theme and will be addressed in much greater detail on the tutorial pages.
For now i can say that architecture of Magento allows different db connections per module.
This is useful when you have especially large data storage needs for specific modules, such as orders or customers.
Also, each module can have different connections for setup, write, read purposes. This is useful in cluster environments.
Each connection is configured to use Zend_Db connection. For our purposes we have extended default functionality of Zend_Db_Adapter_Mysqli and Zend_Db_Adapter_Pdo_Mysql in Varien_Db_Adapter library.
We have a goal of separating the Object Models from Resource Models.
Diagrams will be published, explaining Magento inner architecture.
Basically that means separating the logic specific to resource (db) and logic specific to abstract model.
For example, Customer model has logic that does not depend on database such as sending welcome email. This resides in Object Model.
On opposite, the save method is direct functionality of database, and can be implemented differently with different engines or approaches.
That’s why save() functionality is in Resource Model and Object Model just uses it without actually caring how it does it, only by knowing the right API interface.
The Resource Model can save the customer using regular access to Tables (mysql4) or by calling procedures and relying on triggers (mysql5, mssql, postgresql, oracle, etc.)
PHP code that would support different methods can be totally different, and that’s where the flexibility of Resource Models shines.
More on that in the future