Please don't abuse node references! Part 1

Welcome to the first part of a two part article on a terrible way to use node references, how it was fixed, and the troubles implementing the solution. In this post, I'll provide some background on the problem encountered and talk briefly about the proposed solution.

Background

Recently I was part of a major refactoring project for a large Drupal site. I got to locate areas where we could improve performance (which had become pretty bad with the last feature we rolled out). I was pretty thrilled to get in and not only improve code quality, but also boost performance. Even before this project kicked off, a coworker, Jonathan and I had determined a huge area of the site that could benefit from a major refactor: the way node references were (mis)used.

Technical background

To help explain the exact issue, let's take a look at what content types the client had and how they wanted to use them. There were a variety of "product" content types: Book, Magazine, etc. They also had a Talent content type, meant to represent any person who contributed to one of those product content types. For example, A book can have an author, while someone else provided the foreword, and someone entirely else illustrated the thing, and they also produced the cover art.

This is probably a pretty common scenario, and might normally be structured something like this.

The client wants to be able show who contributed, in what way, to a product on that product's node page. They also want to showcase all the products any particular Talent has contributed to on the Talent's node page. Now here is where the misuse of node references occurs. For each role (author, illustrator, foreword), there was a node reference field on the product, referencing a Talent node.

These fields made showing all of the talent involved on a product pretty straight forward, but made the reverse (Finding all the products a talent contributed to) absolutely terrible. This might have been fine if there were maybe a few roles, but there were around 40 different roles.

Yuck there are too many fields here!

This caused the view displaying all the products a talent contributed to, to have 40+ table joins and a generally very messy query being run. It was clearly not the best structure for this feature.

This can't perform well...

A better approach

Jonathan and I talked about this briefly when we got reports of terrible SQL performance from the view in question. The solution we came up with was to create a field collection that contained an entity reference to a Talent, and an entity reference to a new taxonomy, Product Role.

A breath of fresh air

Where to go from here

In the next post on this topic, I'll get into the nitty-gritty about the pains encountered implementing this solution and reflect on the refactor overall.