Drupal does a pretty good job modeling different types of content through entities. But what if you have some raw content in a custom database table that doesn't fit the requirements of a Drupal entity? You might want to keep all your data denormalized in a single table. Or maybe your data has a composite primary key made up of two or more columns. Let's review some of your options.

Note: This post assumes you are familiar with the Feeds module and are comfortable creating and managing feed importers. For a refresher, see https://www.drupal.org/node/622696.

Entity Primary Keys

Entities in Drupal are only allowed to have a single serial primary key. For example, look at the following data:

Committee (PK)

Employee (PK)

Role

Status

Date Assigned

Management

Bob

Chair

Temp leave

December 17, 2012

Management

Alice

Vice Chair

Active

April 1, 2011

Benefits

Carol

Secretary

Temp leave

July 3, 2014

Business Development

Dave

Vice Chair

Active

March 10, 2011

Benefits

Bob

Chair

Active

December 13, 2013

Management

Dave

Secretary

Active

August 13, 2010

Business Development

Alice

Chair

Active

July 23, 2010

You could model this data using Drupal node, user, and taxonomy entities but it would scatter the data across multiple database tables. Maybe there are other systems that depend on having this data in a single table. Is it possible to have your data and use it too?

Cue the Data module (https://www.drupal.org/project/data). The Data module can manage custom database tables outside of the Drupal schema. With the provided interface, you can define a custom table schema for your raw data. The table can then be exposed to views and other Drupal features.

To model the data above, first download Data and enable the desired modules. This can be done with drush using the command: drush dl schema data; drush en schema data data_ui -y.

Next, navigate as an administrator to Administration => Structure => Data tables (/admin/structure/data). Click "Create new table" and enter the new table information.

Click "Next" to add the columns and then click "Create".

This will create an empty table in the database. From the overview page, you can edit the columns, export the table definition, or view records. You will notice, however, that there is no simple way to add data to the table. You could add rows to the table using a tool like phpmyadmin or by writing SQL insert queries. But what if you want to import data from the Drupal UI without the need  to access your database directly? The Feeds module (https://www.drupal.org/project/feeds) is good at importing content from many different types of sources.

Importing With Feeds

So, can we use Feeds to import data into our custom table? Well, don't get excited just yet.  Feeds makes an unfortunate assumption that you would only need to import data into Drupal entities. Our data table does not fit the requirements to become an entity (a single serial primary key). At Monarch Digital, we had a client in this situation. They had a custom data table and needed to be able to import data from a csv file. To handle this situation, we created a custom Feeds import for complex data tables. You can find the code in a sandbox project at https://www.drupal.org/sandbox/draenen/2442165. (This will most likely become part of the Data module in the near future. See https://www.drupal.org/node/2442185 for status and more info).

The Data Feeds sandbox adds a custom feeds processor to import data into a complex data table. After downloading the sandbox code, configure your feed as normal using the file upload fetcher and CSV parser. For processor, select "Data processor". Under the Data Processor settings, select the data table to use and fill in the other default processor options. If you will be managing the table exclusively through feeds, you should select "Update existing rows" and "Delete non-existent rows".

Next, set up Mappings. This differs a bit from the default mapping and you must must map all the primary keys at a minimum. In the example above, the primary keys are "Committee" and "Employee". After mappings are complete you can import your content at /import or using a import node.

Having any questions or comments on how to make more advanced data tables in Drupal? Please leave your feedback below in the comments section!