Drupal: Fields or Properties (or something else)

Making Drupal scale is hard. It is even harder if you application is big and complex. And one of the main problems is that usually not enough attention is paid to data storage. But let me tell you that the storage model you pick is the backspine of your application, its heart, its soul. 

No fancy UI is ever going to compensate for a slow, unmaintainable and crappy engineered piece of software. 

That's Steve Jobs legacy: Form over Function. Your project has probably more budget for frontend development than for real engineering. Just take a look at this video of Hitler finding out that Lenovo has ditched the ThinkPad (TM) classic touchpad with military grade buttons for the MacBook's really-useful-for-nothing-except-for-browsing single button touchpad.

Side note: the Hitler video seems to be historically accurate as "Steiner" happens to be "Large Project Leader at Lenovo" (linkedin profile) and "Corinna Proctor" is "User Research Manager at Lenovo" (see linkedin profile).

Enough easy chatter and back to the main subject. One of the things you must never do when developing inside Drupal is fighting against Drupal. You have to do it the Drupal way. And sometimes the Drupal way is difficult to understand or not suited for your application, and many people resort to externalization (when you split your application into two or more software stacks because one of them is not suited to do all the jobs but it all revolves around a main system or stack, in this case Drupal). Be careful when you split your application into pieces and make sure they are really independant, or you will end up with a Frankenstein piece of sotware that no one (not even you) can maintain.

Drupal's way of storing data can be tricky for the newcomers. Fields are a paradise for suite builders, until they realise they have built an entity that takes 600 sql statements to persist and that this can be done with 1 single query on a more traditional and straightforward database design. This is a real every day situation. About 2 years ago when I got involved with Drupal for the first time an external site builder helped us build our first Drupal application that we use for managing inscriptions, certificates, attendance and everything related to some events we hold every year. No one could have warned us enough that using an all field approach - and an all site build no programming approach - combined with the slow, memory hungry and CPU hungry PHP stack would be a continuous performance nightmare (PHP7 is going to solve that).

It's been a long way since then and Drupal is not that bad after all if you manage to tame it. Today I am sharing a simple dicision chart to help you plan how you are going to store you data - the Drupal way.

 The storage types, very briefly:

• Properties: these are columns in the entity's primary table. Plain old straightforward storage.
• Fields: these are the drupal way of replacing properties to get revisions, multi-language and multi-value capabilities. It usually consists of at least 2 tables (field_{name} and field_revision_{name}). Operations on fields are heavy because they usually require multiple joins.
• Field based XML/JSON storage: unstructured document storage inside a Drupal field.
• Property based XML/JSON storage: unstructured document storage inside a column of you entity's main table. This is prefered over field based storage depending on the size of this documents and the usage they are given in your application.

On a previous article we demonstrated how easy it was to integrate XML/JSON based storage with Views and other parts of Drupal. Although the original article exposed a JSON approach, we are internally only using XML storage because SQL Server has better indexing support for XML fields and these fields will scale quite well before you need to promote them into computed columns or real properties.