Unlimited arbitrary properties (key/value pairs) for an ActiveRecord model

Question

Using ruby on rails, I have a Customer table that I want to be able to add unlimited properties (key value pairs) to. I'm not sure what the key/value pairs will be yet so I'm not sure how to do this. For example, one customer could be:

• Customer 1 properties:
  • color: 'yellow'
  • brand: 'nike'
  • sales: '33'
• Customer 2 properties:
  • color: 'red'
  • phone_number: '1111111111'
  • purchases: '2'

Basically, customers can have any number of properties in a key/value pair.

How can I do this?

Answer 1

The "traditional" way to do this is with the Entity-Attribute-Value, or EAV pattern. As the name suggests, you'll create a new table with three columns: one for the "entity," which in this case is the Customer, one for the "attribute" name or key, and one for the value. So you'd have a table like this:

customer_properties
+----+-------------+--------------+------------+
| id | customer_id | key          | value      |
+----+-------------+--------------+------------+
|  1 |           1 | color        | yellow     |
|  2 |           1 | brand        | nike       |
|  3 |           1 | sales        | 33         |
|  4 |           2 | color        | red        |
|  5 |           2 | phone_number | 1111111111 |
|  6 |           2 | purchases    | 2          |
+----+-------------+--------------+------------+

You'll definitely want an INDEX on key and maybe on value (and customer_id, of course, but Rails will do that for you when you use relation or belongs_to in your migration).

Then in your models:

# customer.rb
class Customer < ActiveRecord::Base
  has_many :customer_properties
end

# customer_property.rb
class CustomerProperty < ActiveRecord::Base
  belongs_to :customer
end

This enables usage like this:

customer = Customer.joins(:customer_properties)
             .includes(:customer_properties)
             .where(customer_properties: { key: "brand", value: "nike" })
             .first

customer.customer_properties.each_with_object({}) do |prop, hsh|
  hsh[prop.key] = prop.val
end
# => { "color" => "yellow",
#      "brand" => "nike",
#      "sales" => "33" }

customer.customer_properties.create(key: "email", value: "[email protected]")
# => #<CustomerProperty id: 7, customer_id: 1, key: "email", ...>

As database design goes this is pretty solid, but as you can see it has some limitations: In particular, it's cumbersome. Also, you're restricted to a single value type (:string/VARCHAR is common). If you go this route you'll likely want to define some convenience methods on Customer to make accessing and updating properties less cumbersome. I'm guessing there are probably gems specifically for making the EAV pattern work nicely with ActiveRecord, but I don't know them off the top of my head and I hope you'll forgive me for not googling, since I'm mobile.

As Brad Werth points out, if you just need to store arbitrary properties and not query by them, serialize is a great alternative, and if you use PostgreSQL even the querying problem is surmountable thanks to its great hstore feature.

Good luck!

Answer 2

You may want to look into the hydra_attribute gem, which is an implementation of the Entity-Attribute-Value (EAV) pattern for ActiveRecord models.