Active Record and DB normalization


#1

Folks,

I have a need to normalize some DB tables, and I’m having difficulty
working out how ActiveRecord models should handle this need. Any
advice would be greatly appreciated.

The user model was previously very simple, which allowed for simple
data binding to the view.

class User < ActiveRecord::Base
attr_accessor :password, :password_confirmation

attr_accessible :name, :email_address, :password, :password_confirmation

end

<% remote_form_for :user, :url => { :controller => :user, :action
=> :add } do |u| %>
Display name:<%= u.text_field :name %>
Email address:<%= u.text_field :email %>
Password:<%= u.password_field :password %>
Confirm password:<%=
u.password_field :password_confirmation %>

<% end %>

I’m now adding the ability for a user to invite another user to join
the site (by entering their email address). The 2 approaches I
considered were:

  1. Create an invitation model that joins 2 users. All user fields
    except for email address would have to be nullable, so my model
    validation would be non-existent and this whole direction feels ugly.
  2. Create an invitation model that joins a user and an email address.
    The user and invitation models now both have an email address, and
    both for performance and to maintain a clean data model the email
    address should be normalized into its own model.

My challenge with option 2 is that, while the email address is still
logically a part of the user model and needs to be data bound to the
view in the same way as before, this requires a bit of mucking around
and I’m not sure if there is an easier way. My approach looks like
this:

class Email < ActiveRecord::Base
attr_accessible :email_address

end
class User < ActiveRecord::Base
belongs_to: email
attr_accessor :email_address, :password, :password_confirmation

attr_accessible :name, :email_address, :password, :password_confirmation

Move email_address between accessor (needed for data binding) and

email instance

before_validation_on_create
:create_email_instance_from_email_address_attr
after_find :set_email_address_attr_from_email_instance

def validate
# Validate that no user (except for this one, in case of update)
has this email address,
# as validate_uniqueness_of cannot be used
end

end

belongs_to is normally used to link 2 logically separate models,
whereas here the 2 model design is purely a result of normalization
from multiple tables needing to store email addresses. Is there a
better way?

Any insight is greatly appreciated.

Regards,
Chris


#2

Does this work:

class User < AR::Base
has_many :users
has_many :users, :through => :invitations

end

The join seems self-referential and doesn’t really require
duplication or normalization. No?

On Jul 4, 2007, at 3:15 PM, Chris wrote:

class User < ActiveRecord::Base
Email address:<%= u.text_field :email %>
except for email address would have to be nullable, so my model
this:
on
has this email address,

Any insight is greatly appreciated.

Regards,
Chris

Steve R.
removed_email_address@domain.invalid
http://www.calicowebdev.com


#3

I haven’t worked with associations until now, and your approach is a
clear improvement. Thanks!

But I’m hoping to avoid making the invitee a user, because an invitee
doesn’t necessarily have an account yet, so every column except for
the email address would need to be nullable, making my user model
messy and validation difficult.

Here is a tweaked version of your suggestion, making the invitee an
email instance only:

class User < AR::Base
has_many :emails, :through => :invitations

end

Given that I need an email model for invitations, I feel the need to
normalize the email_address attribute on the email model too. Am I
causing too much pain just for the sake of “correctness”? If I
continue down this path, the problem remains how to represent the
email address as a property of the user so that it can be easily bound
to user views.

One approach would be to lazy load the email address, by implementing
an equivalent interface to attr_accessor :email_address myself.

class User < AR::Base

Add an email_id column to the User table, which the model doesn’t

explore further

Used for data binding to views only

attr_accessible :email_address

has_many :emails, :through => :invitations

def email_address
@email_address = Email.find(@email_id).email_address if
@email_address.nil?
@email_address
end

def email_address=(value)
@email_address = value
@email_id = Email.find_or_create(value).id
end

end

The end result is a user model which hides any normalization,
presenting a denormalized interface to the controller. What do you
think?

Cheers,
Chris


#4

I’m not partial to tables that store snippets of information like
just an email address. That’s just my bias. You are paying for that
normalization with join tables. So, the argument would go that the
normal form will reduce the risk of data duplication. A counter-
argument is that the more join tables you use, the more vulnerable
you are to relational integrity problems.

Just a thought.


#5

To be honest, I’m not a fan of an email address table either. :slight_smile:

I’m worried that by re-using the user model to store invitees (which
are just email addresses), all other fields in the user model must be
nullable, and the validation will need to be hand-coded and only kick
in when the is_registered field is set. Avoiding this is the only
reason I’m considering this normalization.

I could store the email address of the invitee directly in the
invitation table, but then getting a list of invitations for an
invitee will require a join that uses a string comparison.

Thanks for the feedback! Seeing how other people approach the same
problem is a fantastic learning tool.

Cheers,
Chris


#6

If you’re using Rails validations instead of database ones, you can
add something like:

validates_presence_of :address, :if => :real_user?

Does that sort of thing work?