Anomalous

I have multiple pieces of data to store that all relate to one row in my database, but I don’t want to create a completely separate data model and/or table. Is it possible to store this data in a single column within the same database row?

I recently had this exact problem, I had a single row in my database that referred to one or many (I am using Rails STI, the amount of data depends on the objects stored within the row) other pieces of arbitrary data. This is most commonly referred to as ‘metadata’ and would typically have its own table of key value pairs and a foreign key; but in my case I wont need it that often, and didn’t want to create another table to handle such small amounts of data. So I thought “there is no way that someone else hasn’t faced this issue”, and naturally turned to Google.

I was surprised at how little I found; what I did find were different discussions about creating metadata for plugins, or even storing JSON in the column (not bad, but how do you search for data in JSON from MySQL?); nothing too helpful, leaving me to fend for myself.

After days of racking my brain I had a thought; XML has standards, it has practical uses, and it’s widely used and understood, so why not store the information I need as a mini XML document? This would allow me to store as much information as I want to, in one column – and using methods within MySQL I can even search the data. Below I will walk you through a simple Rails solution demonstrating how to use this metadata method, built on top of simple Single Table Inheritance (STI).

This example will use vehicles (cars and trucks) as our STI model, and our metadata will store information about each cars specifications.

Basics of our STI classes, with metadata column

Writing to our metadata column
In order for the setter to write correctly, it expects a hash. The data submitted from a form (POST data) is already in the form of a hash, but the reason for implementing this method was to allow for more than one value to be stored here, this requires a little work on our end. Lets say we want to store information about the car that will go on the sticker within a dealership.. We never know what could end up on that sticker, but we have one column to store it.

In order to control what happens to the data within this field, we will need to write a custom setter for the metadata attribute. The setter method that we create will allow us to handle the incoming data in any way we want. The method we need to add to our Vehicle class would look like this

This setter expects a hash from our form, this is because of the method we use to convert the data to XML. Since we are providing the hash to the setter we will need to name our form fields accordingly, this can be accomplished like so

When this form is submitted, the following will be written to the database

As you can see, it looks like a mini XML document, header and all. The beauty of it, Rails has built in functionality to read this data, and SQL has built in methods to search this data!

Reading our metadata column
Now, rails supports all the data types that your database supports as far as retrieving and displaying data, and the default getter methods work perfect for those – unfortunately XML is not one of them. In order to get the data out of our column, in a form that is easily readable by rails, we will add the following custom getter method to our Vehicle class

This getter will retrieve the XML in the column, and return it as a Ruby hash, resembling

Data is this form should be quite familiar, the Hash object is widely used within Ruby and Rails applications. And just like any other Hash, when we want to display this information, we could use something as simple as

Searching this data using SQL
An important aspect of any data, is that it is searchable. Unlike JSON, XML can be searched within a database column using XPATH. Lets say, for instance, that we want to find all cars that are under $10,000.. We can use the following to accomplish that

So there you have it, a relatively easy, clean, scalable way to store arbitrary data associated with a single database row in your Rails applications. Please understand that this is a basic example, if you were to use such code you would want to make sure to take care of the appropriate error checking, and testing.

If you would like to view this post as a working example, I posted a sample app on github.

I’m sure there are many ways of accomplishing this, this method worked for me.. Do you have a better method? Suggestions as to how I can improve this one?