Recently, I've been working at ways to make more efficient database modules, specifically on a recent application as it needs the ability to either write the data to a database (must be any compatible database) or alternatively, either send the data via encrypted email or store on the server in xml based files.

I've started with a class representing each table with insert, update, and delete methods, and specific finder methods (findById, findByName, etc). On top of this, I have a set of classes representing the divided sections of the data which are grouped together. This has 2 public functions, store and retrieve, which call private methods that know how to access each of the table data gateways. These classes also store foreign keys, which help keep track of foreign key relationships. Finally, at the top layer, there's a single object which calls these sections, giving a single point of access to the layer via a store method which takes an array of the key values of data, and also returns the requested data from a retreive method that accepts an id as an array.

So far this has worked good with ADODB being used in the table data gateway objects to handle database interaction using standard SQL that should be compatible accross database types (and using the ADODB methods to generate SQL queries). However, I can edit the objects and swap ADODB for PEAR:B, or even some custom made DB abstraction layer.

Only problem is that there's a class for each table, and it's tedious if dealing with a large databse with complex table relationships. I refactored most of these classes into single classes based on similarities such as foreign key relationships and such, but there still needs to be derived objects to handle specific finder methods such as findByLastName, which wouldn't exist in something such as an assets table.

Also, joins are tricky too. I'd like to keep them made through the data access layer to avoid writing any direct SQL, but doing it by combining a set of objects seems haphazard and inefficient as it requires multiple queries rather than just one.

How do you do it?