Building the Mapping
When you map to a relational database, there are essentially three situations that you encounter:
You choose the schema yourself.
You have to map to an existing schema, which can't be changed.
You have to map to an existing schema, but changes to it are negotiable.
The simplest case is where you're doing the schema yourself and have little to moderate complexity in your domain logic, resulting in a Transaction Script (110) or Table Module (125) design. In this case you can design the tables around the data using classic database design techniques. Use a Row Data Gateway (152) or Table Data Gateway (144) to pull the SQL away from the domain logic.
If you're using a Domain Model (116), you should beware of a design that looks like a database design. In this case build your Domain Model (116) without regard to the database so that you can best simplify the domain logic. Treat the database design as a way of persisting the objects' data. Data Mapper (165) gives you the most flexibility here, but it's more complex. If a database design isomorphic to the Domain Model (116) makes sense, you might consider an Active Record (160) instead.
Although building the model first is a reasonable way of thinking about it, this advice only applies within short iterative cycles. Spending six months building a database-free Domain Model (116) and then deciding to persist it once you're done is highly risky. The danger is that the resulting design will have crippling performance problems that take too much refactoring to fix. Instead, build up the database with each iteration, of no more than six weeks in length and preferably fewer. That way you'll get rapid and continuous feedback about how your database interactions work in practice. Within any particular task you should think about the Domain Model (116) first, but integrate each piece of Domain Model (116) in the database as you go.
When the schema's already there, your choices are similar but the process is slightly different. With simple domain logic you build Row Data Gateway (152) or Table Data Gateway (144) classes that mimic the database, and layer domain logic on top of that. With more complex domain logic you'll need a Domain Model (116), which is highly unlikely to match the database design. Therefore, gradually build up the Domain Model (116) and include Data Mappers (165) to persist the data to the existing database.
Occasionally I run into situations where the same kind of data needs to be pulled from more than one source. There may be multiple databases that hold the same data but have small differences in the schema because of some copy and paste reuse. (In this situation the amount of annoyance is inversely proportional to the amount of the difference.) Another possibility is using different mechanisms, storing the data sometimes in a database and sometimes in messages. You may want to pull similar data from both XML messages, CICS transactions, and relational tables.
The simplest option is to have multiple mapping layers, one for each data source. However, if data is very similar this can lead to a lot of duplication. In this situation you might consider a two-step mapping scheme. The first step converts data from the in-memory schema to a logical data store schema. The logical data store schema is designed to maximize the similarities in the data source formats. The second step maps from the logical data store schema to the actual physical data store schema. This second step contains the differences.
The extra step only pays for itself when you have many commonalities, so you should use it when you have similar but annoyingly different physical data stores. Treat the mapping from the logical data store to the physical data store as a Gateway (466) and use any of the mapping techniques to map from the application logic to the logical data store.