Best design pattern for database table joins

Question

I'm looking at programming/design patterns for the model layer of my application, and am wondering which one fits best with the situation where you are doing a retrieval that involves joins across multiple tables.

For example, suppose you have the following tables/relations: Customer --> 1..n Accounts --> 0..n Features

where a Feature could be a cheque book, or some premium product like free travel insurance.

Then I want to do getCustomersForFeature() to retrieve all customers with accounts that have free travel insurance.

Using ActiveRecord or Data Access Object doesn't seem to fit, as these generally focus on one class per table; likewise for Data Mapper. I realise I could break it down into operations for each table, e.g. getAccountsForFeature() and getCustomersForAccount(), but I want to do the retrieval in one hit.

If we were to "bend" the one-class-per-table patterns and use the Data Access Object pattern, say, should the getCustomersForFeature() method go on CustomerDAO or FeatureDAO? But this doesn't feel right to me because you would be polluting your DAOs with knowledge of other tables.

Suggestions please.

Answer 1

In Model-Driven Design, you have logical Entities in your application, and these Entities can map to multiple tables in the physical database. Of course you need to run more complex SQL to fetch a complete Entity, and the Model class is the place where these relationships are implemented.

I think ActiveRecord and their ilk are fine to use for simple queries against a single table, but trying for force the use of these patterns for complex queries is too difficult. Fortunately, we already have a concise, domain-specific language that you can use to specify complex queries: SQL.

So in your Model class, you'd have methods to perform logical application-level tasks, such as getCustomersForFeature(). In the code for that method, you should write a specific query, either with ActiveRecord methods or with straight SQL if needed. Thus the concrete design of your database is encapsulated in one place, in the Model class.

This means that we need to break the coupling between the Model and the Table. The OO relationship between a Model and an ActiveRecord class is not IS-A -- it's HAS-A (or has-many).

---

Re your comment: So what's our Model? If your application primarily needs to work with customers as an entity, and treats features as more or less an attribute of customers, then yes your Model would be Customers, and it would hide the fact that features are stored in a separate table in the database. The Customers model would internally use either ActiveRecord or plain SQL to gather the needed data to provide a complete view of the complex object that is a customer with its associated multi-valued attributes.

However, what if your application also has the need to work with Features directly? For instance, an administrator's screen where you can get reports based on features or create new features. Then it would be clumsy to access features through the Customer model. So you'd need a Features model after all. Only it would have different methods to implement the operations you need to do with Features.

Each model class should expose an API of just the things you need to do with that model. There's no need even to be symmetrical. Just because your Customer model can fetch all customers who have a given feature, doesn't necessarily mean your Features model needs to fetch all features for a given customer. Follow the YAGNI rule.

But after you've created your Customer model and your Features model, doesn't this lead to duplication of the logic that knows about relationships between tables? Yes, it could. This is one of the many problems that fall under the scope of the object-relational impedance mismatch.