ruby-on-rails-4rails-activerecordrails-migrationsrails-postgresql

How should I define a constraint for unique array values within a column in Rails?


I am trying to store some leads in my Rails CRM Application. Here's the sample Lead model.

class Lead < ActiveRecord::Base
  belongs_to :campaign
  validates_presence_of :name
end

Here's a sample migration that I'm using to store my leads in the database.

class CreateLeads < ActiveRecord::Migration
  def change
    create_table :leads do |t|
      t.string :name,          null: false, default: ""
      t.string :contacts,      null: false, array: true, default: []
      t.string :emails,        null: false, array: true, default: []
      t.string :budget,        null: false, default: "Not Specified"
      t.string :requirements,  null: false, array: true, default: []
      t.timestamps null: false
    end
  end
end

There are possibilities that my leads have multiple email addresses, contact numbers and requirements. Therefore, I decided to implement the forementioned colums as arrays.

I would like to ensure that none of a lead's email addresses or contacts are used to create a new lead's row in the database.

Should I implement this using the model or via the migration? Please direct me on how to implement this the rails way.


Solution

  • Arrays within Row Implementation - Problematic

    Arrays within tables? Relational databases are built to hold records in tables. Both humans and databases understand this. Unless there are cogent reasons for doing so, don't do it.

    I haven't looked at the underlying implementation, but even if the implementation is the SAME, it's an approach I haven't seen out there in the world.

    Solution: Add a new table

    Use another table for lead email addresses. i.e. emails. Add the following columns: id, lead_id and email. and link via lead_id foreign key. then when creating a new lead you will have to validate that lead by by checking that emails table whether that email address already exists.

    You can add a DB constraint checking whether the email already exists, or you can validate via models, or perhaps a combination of both?

    Caveat

    but googlers are coming here looking for a solution for how to add a unique constraint to an array column in Rails/AR. Would be nice if there was still a solution for that other than saying don't do this

    I answered OP's very specific question - i.e. "is this a good idea? is it the rails way?". If Googlers want an answer to an entirely different question - they are more than welcome to continue googling, or to ask a new question on StackOverflow.