Design recommendations for authenticating users with lots of

Rails
1

I’ve been struggling a bit trying to figure out the best way to
design/implement a system with authentication/authorization, and was
hoping some of you may be able to offer some advice…

At the moment, I have a system with 4 different types of users -
clients, administrators, sales_reps, and public_users. I’m using
“Authorizing Users with Roles” from the Rails Recipes book, and it’s
working well, the only problem I’m having is the following:

I have a Users table which contains pretty much the minimum amount of
information required to allow users to login:

def self.up
create_table :users do |t|
t.column :username, :string
t.column :password_salt, :string
t.column :password_hash, :string
t.column :created_at, :datetime
t.column :updated_at, :datetime
t.column :last_login_date, :datetime
end

The problem is that some users (such as sales reps), need many more
attributes than are provided by the User table, such as
primary_market, secondary_market, previous_employer, current_employer,
essay, referral_source, website, comments, etc… So what I’m
struggling with is how to add these attributes… Do I:

a) add all the attributes to the users table - this is the easiest
solution, since then I can consider everyone as a user, the only
difference being that some users will actually make use of the
additional attributes, while others (such as administrators) won’t
need the extra info.

b) add as many common attributes (such as first_name, last_name, date
of birth) to the Users table and create another table for additional
information specific to the particular user type (ie add
primary_market, essay, etc to a “sales_rep_info” table)

c) ??

right now I’m using option B, but what I find confusing is if, for
example, I have a has_and_belongs_to_many relationship for sales reps,
such as primary_market (can be a combination of “automotive”,
“electrical”, “industrial”, etc), do I associate the foreign key of
the primary_market join table with the sales_rep_info table, or do I
associate it with the users table? Or do I associate with both
tables, (ie, use the same id value for sales_rep_info as I do for the
entry in the users table).

I’m thinking that I should associate it with the users table, since
that should be the main entry point for updating/inserting/modifying
user information… But then the problem is that I have a bunch of
associations attached to the users table, and then I might have
something like primary_city_id, which is something specific to a
sales_rep, so ideally, that should be referenced in the sales_rep_info
table, not the users table… So then I end up with a mixture of
associations spread through both the users table and the
sales_rep_table, some of which contain information specific to the
type of user, and some of which are generic relations.

Now that I’ve written things down, it seems like I should keep all
generic associations tied to the user table (ie roles, rights, etc),
and use the sales_rep_info table for informaiton specific to the sales
reps…

so if anyone can make heads or tails of what I’m talking about, I
would appreciate some advice or direction. Thanks for your help,

Mike

2

You might want to look at the user engine

It offers a lot of what you want

Allowing access control over controllers and actions

http://api.rails-engines.org/user_engine

after you install take the time to look around in the different areas to
get
a good understanding of whats happening

3

On Monday, May 29, 2006, at 4:19 PM, Mike G. wrote:

information required to allow users to login:

need the extra info.
such as primary_market (can be a combination of “automotive”,
something like primary_city_id, which is something specific to a

so if anyone can make heads or tails of what I’m talking about, I
would appreciate some advice or direction. Thanks for your help,

Mike

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Your option A is essentially a single table inheritance scheme, which
works pretty well. The fact that some attributes can have multiple
values suggests that you might be able to use acts_as_taggable for your
primary_markets (or anything else that can have multiple values).

_Kevin

4

Mike G. wrote:

At the moment, I have a system with 4 different types of users -
clients, administrators, sales_reps, and public_users. I’m using
“Authorizing Users with Roles” from the Rails Recipes book, and it’s
working well, the only problem I’m having is the following:

I typically handle these types of situations with a one-to-one
relationship. Keep all of your authentication / authorization data in a
core “users” table, along with permissions and such in however many
necessary tables (linked into “users”). Additional user data can be
linked to separate, custom tables. These tables, of course, would
contain a “user_id” attribute tying back to User. This way you can
always instantiate a User object if necessary. Clean, no duplication of
code, and the User object always works how you want without a bunch of
null data. If you didn’t like having separate classes wanted to
“combine” them, you could always have them subclass User and create a
“pure” User object whenever it’s needed. (Although if properly
subclassed you should be able to use the specific classes wherever
needed.)

-Curtis

5

Hi Mike,

I would recommend splitting out all the data into different tables,
trying
to keep as much redundant data to a minimum as possible. This through
relations. I would also check out some 3NF documentation, it’s
basically a
set of rules that help design relational DB’s. What you get from this
approach makes worlds of difference when adding features and trying to
run
stats on your users.

Link : Putting a Database in Third Normal Form (3NF)

Regards,

Phil Johnston II

6

Why not put all the role specific data in a “Properties” table with
property_name and property_value columns, each entry associated with a
specific user? This would allow you to store exactly what you need for
each
role without having to create a specific table for each of them. The
only
downsides I can think of are that all your values would be strings and
you
don’t have the rigid control you would have from putting these values
into
specifically designed tables.

While having to check that all of your data is valid within code rather
then
handing that off to the database does add some complexity to the app, it
would seem that having individual tables and objects for each type of
user
would add complexity as well.

I assume that since this hasn’t been suggested yet that there’s
something
I’m not seeing here…I’m not a DBA by any stretch of the imagination.

7

On 5/29/06, Clayton C. [email protected] wrote:

after you install take the time to look around in the different areas to get
a good understanding of whats happening

thanks for the suggestion Clayton, although the Authorization recipe
from Rails Recipes seems to be doing the trick (I wanted to stay away
from using plugins/generators/engines/etc, since I wanted to have
complete control over the authentication scheme without having to pull
apart someone else’s code).

On 29 May 2006 20:35:37 -0000, Kevin O.
[email protected] wrote:

On Monday, May 29, 2006, at 4:19 PM, Mike G. wrote:

a) add all the attributes to the users table - this is the easiest
solution, since then I can consider everyone as a user, the only
difference being that some users will actually make use of the
additional attributes, while others (such as administrators) won’t
need the extra info.

Your option A is essentially a single table inheritance scheme, which
works pretty well. The fact that some attributes can have multiple
values suggests that you might be able to use acts_as_taggable for your
primary_markets (or anything else that can have multiple values).

yeah, I originally thought about doing this using single table
inheritance, but I didn’t like the fact that there would be so many
unused columns and sparse entries in the database, but now things are
starting to get messy… I’m thinking now that maybe I should just save
myself a lot of pain and switch to single table inheritance, even if
it is kinda wasteful (and I should keep reminding myself that
premature optimization is the root of all evil :wink:

I’ll have another look at single table inheritance and see how I can
change my data model around to fit it.

Thanks for the advice,

Mike

8

(I wanted to stay away
from using plugins/generators/engines/etc, since I wanted to have
complete control over the authentication scheme without having to pull
apart someone else’s code).

acts_as_authenticated is ridiculously simple. just run it then do a svn
diff and see. altho i was able to modify it to work with an existing
userbase from a Drupal in about 2 minutes, since the code was so easy to
navigate…

9

On 5/30/06, Curtis S. [email protected] wrote:

linked to separate, custom tables. These tables, of course, would
contain a “user_id” attribute tying back to User. This way you can
always instantiate a User object if necessary. Clean, no duplication of
code, and the User object always works how you want without a bunch of
null data. If you didn’t like having separate classes wanted to
“combine” them, you could always have them subclass User and create a
“pure” User object whenever it’s needed. (Although if properly
subclassed you should be able to use the specific classes wherever needed.)

I’ve been wavering back and forth between using the single table
inheritance method and the method you prescribe (which is what I’m
currently using now)… Initially I decided to use a separate table for
the information specific to different types of users (ie sales_reps),
since I didn’t like the idea of having tons of null values in the User
table… After posting to the list, I started thinking about switching
to using single table inheritance, but in the end I’ve decided to
stick with using a separate table, since at this point, it would take
a lot of work do migrate my data into a single table inheritance
model, and it’ll introduce redundancy into my database. It also seems
that I’ve got more responses from the mailing list suggesting to use a
separate table, so it at least reassures me that what I’m doing isn’t
completely wrong :wink:

On 5/30/06, Jeff A. [email protected] wrote:

would seem that having individual tables and objects for each type of user
would add complexity as well.

I assume that since this hasn’t been suggested yet that there’s something
I’m not seeing here…I’m not a DBA by any stretch of the imagination.

thanks for the suggestion Jeff - I might use your idea in the future,
but for now I think I’m gonna stick with storing extra information
into a specific table…

Mike

10

Alright, I seem to have run into an area that’s making things a little
bit complicated…

RIght now I have my User model, which contains password, username
(e-mail address), first_name, last_name, and some other common
elements. I also have a SalesRep model which belongs_to Users and
contains additional information specific to a sales rep… I’m now
trying to display a paginated form of sales reps with a certain
status_id (status_id is an attribute of the sales_rep table). I was
previously using this:

if @status_id == "flagged"
  conditions = "flagged = true"
elsif @status_id == "all"
  conditions = nil
else
  @status_id = @status_id.to_i
  conditions = ["status_id = ?", @status_id]
end

@sales_rep_pages, @sales_reps = paginate(:sales_reps,
:conditions => conditions,
:per_page => @page_size )

which worked fine, but now I need to be able to filter the displayed
data by some of the attributes of the User model (first_name,
last_name), as well as some of the attributes of the SalesRep model…
So instead of paginating the sales_reps model, I’m now doing the
following:

if @status_id == "flagged"
  conditions = "sales_reps.flagged = true"
elsif @status_id == "all"
  all_statuses = Status.find_all.collect {|status| status.id

}.join(",")
conditions = “application_forms.status_id in (#{all_statuses})”
else
@status_id = @status_id.to_i
conditions = [“sales_reps.status_id = ?”, @status_id]
end

@user_pages, @users = paginate(:users,
                               :conditions => conditions,
                               :include => 'sales_rep',
                               :per_page => @page_size )

so I’ve had to change the paginate method to include the sales_rep
association, which works fine, but unfortunately, if I use “conditions
= nil” to return records of all status_id’s the query is extremely
slow… So I’ve had to use ‘conditions = "sales_reps.status_id in
#{all_statuses}’ instead, which works, but I’m wondering if there’s a
better way to do this… If anyone has any suggestions, please let me
know (and of course switching to Single table inheritance would fix
this, but I’d still like to implement this using the current database
model, before attempting to change everything around). Thanks,

Mike