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

Question

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.

Answer 1

Arrays within Row Implementation - Problematic

Arrays within tables? Please avoid if possible. Relational databases are built to hold records in tables. Both humans and databases understand this. Unless there are some reason cogent reasons for doing so, I cannot see a good reason to dump a whole bunch of records in an array, on the same record, where it is more difficult to parse/find/validate etc. i don't think this situation can justify that design decision. I haven't looked at the underlying implementation, but even if the implementation is the SAME as if you had added a separate table, this might be more confusing for programmers working on your application.

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?