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 C#/Linq to SQL it would be something like the following. (I'm assuming there's actually a lookup table of feature types, so that you have a standard list of your feature types and then their relationship to an account is separate, so FeatureTypeId would be your FK value, maybe selected from a drop-down list or something.)

// or whatever data type your keys are in
public IEnumerable<Customer> getCustomersForFeature(int featureTypeId)
{
    return from feature in dbContext.Features
           where feature.FeatureTypeId == featureTypeId
           select getCustomer(feature.Account.Customer.Id);
}

At some level your DAO/BAO have to be aware of the relationships between your objects, so the fact that this is a grandparent relationship shouldn't be too scary.

As to where it belongs in your BAO structure, an argument could probably be made either way. I'd probably put it on Customer, since that's ultimately where I'm trying to get to.

Edit: As Toby pointed out, the relationships are bidirectional; again in Linq, you could go the other way as well:

// or whatever data type your keys are in
public IEnumerable<Customer> getCustomersForFeature(int featureTypeId)
{
    return from customer in dbContext.Customers
           from account in customer.Accounts
           from feature in account.Features
           where feature.FeatureTypeId == featureTypeId
           select getCustomer(customer.Id);
}

Any other ORM should have very similar behavior, even though the syntax and structure will vary.

Answer 2

The way Active Record in Rails models these is by allowing Customer object to have_many Accounts - which basically translates to a collection of Account objects, which in turn have a collection of Features. The relationships can be bidirectional, so each AR model can "know" about it's relationships, depending on your needs.

I think it is fine for an object to have knowledge of other tables, as such relationships are fundamental to both OO and RDBMS/SQL.

Answer 3

In general, model your data according to the capabilities of the SQL database that you are using. Ignore the ORMs, Mappers, etc.

Once you have a good data model, if ActiveRecord or DAO will not access it in the way you want, then bypass them by writing SQL.

Object Relational Mapping is supposed to make programming with databases easier, but because or the differences between the models, sometimes the simple way is to go straight to SQL.

Answer 4

Agreed to GalacticCowboy that ORM allows to separate database class mapping details from object-oriented queries. Just want to provide an example for java hibernate orm - there are number of ways to define association mappings and following object-oriented HQL query works fine above all of them

select c from Customer c left join c.accounts a left join a.features f where f.name = 'cool'

Note that 'Customer' is a class name here and 'c.accounts', 'a.features' and 'f.name' are class-level property names, i.e. no database-specific details are mentioned here.

Answer 5

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.

Answer 6

I think this is classic problem ( at least for me ) , where you want to represent your data model in your application. According to my own experience there is always akward sitution like you described. My solution is very similar with some answer above. Make simple class that one to one with your table and if that table have relationship with another table , then make as a property in your class.

And about your saying 'I want to do the retrieval in one hit.' , I think you should write custom SQL Query or perhaps using LINQ.

Answer 7

While Object to realtional stuff is great for CRUD type table maintainance I find nothing beats actual SQL when it comes to complex queries tables.

The ORM model is just an abstraction too far.

Answer 8

Requirements oten decide it for you. Either by your deadline, your work culture or your project requirements. Your deadline might indicate, "code to tools" and cut features. Your staff might require "no new libraries or failure modes." In my own environment, you'd hear me tell this story:

"...it wouldn't do to introduce new ORM libraries, so those are out. Deadlines would indicate I shouldn't start teaching myself how to produce SQL views. My work culture informs me to get it done quickly. My architecture groan at the serializing whole tables and my performance requirements indicate I should not cache any of this data..."

SQL views might provide a good way to abstract the joins from your objects, and could allow you to change your schema more independently of your application code. Updating views is very tricky, tho, depending on your database. If DB portability is important, that probably informs your design a great deal.

You might find harmony with a hybrid approach. If you use a table gateway, there is no reason to slavishly aggregate it into every application level view of the table. Why not use table gateways or active records for updating items in the table-scope, and custom classes that deal with the view oriented queries?

Answer 9

I have spent some more time reading around the subject (including the Domain Driven Design mini-book referenced in Bill's answer). There is an Aggregate concept in that book, which most closely represents what I am trying to achieve; the Customer encapsulates and controls access to the Account and Feature. If we stick with the Domain Driven Design approach we could use a Repository to control retrieval of the Customer, and it seems to me that this is where knowledge of the database structure would be encapsulated.

I also had another look at my copy of Patterns of Enterprise Application Architecture, and while it seems that the ActiveRecord pattern is intended for a direct mapping of class to database table, if you chose not to follow the Repository approach from DDD then Data Mapper would be suitable for a composite mapping.

Thanks all for your contributions. I have voted up the answers that contributed to this conclusion. As this may not be the last point in this discussion, I have flagged this answer as Community Wiki.