Rails - May 2006 - design recommendations for authenticating users with lots of different attributes..?

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

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






-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Mike Garey
Sent: Monday, May 29, 2006 1:19 PM
To: rails@lists.rubyonrails.org
Subject: [Rails] design recommendations for authenticating users with lots
of different attributes..?

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
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Monday, May 29, 2006, at 4:19 PM, Mike Garey wrote:
>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
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>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

-- 
Posted with http://DevLists.com.  Sign up and save your mailbox.

On 5/29/06, Clayton Cottingham <dr.frog@shaw.ca>
wrote:
> 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
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 Olbrich
<devlists-rubyonrails@devlists.com> wrote:
>
> On Monday, May 29, 2006, at 4:19 PM, Mike Garey wrote:
<snip>
> >
> >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 ;)

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

Mike Garey 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

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 : http://databases.about.com/od/specificproducts/l/aa3nf.htm

Regards,

Phil Johnston II


On 5/29/06, Mike Garey <random52k@gmail.com>
wrote:
>
> 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
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060530/85a3ca56/attachment.html

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.

On 5/30/06, Philip Johnston <johnstpd@gmail.com>
wrote:
>
> 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 : http://databases.about.com/od/specificproducts/l/aa3nf.htm
>
> Regards,
>
> Phil Johnston II
>
>
>
> On 5/29/06, Mike Garey <random52k@gmail.com> wrote:
> >
> > 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
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060530/6be60bb1/attachment-0001.html

On 5/30/06, Curtis Spendlove <cuspendlove@gmail.com>
wrote:
> Mike Garey 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.)
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 ;)


On 5/30/06, Jeff Avallone <jeff.avallone@gmail.com>
wrote:
> 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.
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

> (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..

-- 
Posted via http://www.ruby-forum.com/.

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