users entity/table - Modeling this relationship

OK. This may is not a not-smart question but I'll ask it anyway. Been working on this project's model too long to be ashamed... The whole thing works great without the 'users' table... yipee for me :-/

So, how do I model this properly? The ACL model looks like this: (FYI "->" means "greater than" in this hierarchical view) and has certain permissions that are greater than other users

System Admin => Shops => Shop Admins => Workers => Clients => Anonymous  

How do I model the 'users' when there are several types of 'user' that each have their own table?

For instance, lets say there are 2 ACL roles for 'users'. " SHOP WORKER", and "CLIENT". How do I deal with a 'users' table in this scenario? Is it by assigning a 'user_type' in the 'users' table liker this:

table .users
- user_id = "1"
- user_name = "Joe"
- user_type = "5" (where "user_type"=5 d开发者_StackOverflow社区elivers the needed powers...)

Or am I being a spaz? Am I missing something here? I need ONE "USERS" table with multiple different types of users that all need different types of info that need to be part of their profile, which would seem to make different tables for each user_type make sense... "SHOP WORKERS" get to add their data to their table and "CLIENTS" get to add their data to their own table.

Please tell me how to make this work. My brain is apparently broken and feel a little retarded after pondering this...

Based on your question, it sounds like you're trying to build an ACL and a metadata storage system at the same time. This can be a little tricky to sort out.

Generally in a role-based system you have several patterns to follow, but usually some combination of the following:

• Users have system-wide privileges based on a type of authentication level.
• Users have system-wide privileges that are granted by the user having one or more roles.
• Users have different privileges based on their authentication level in the context of specific resources.

You might be describing the first or second case depending on the nuances of it. Here's an example of the first:

class User < ActiveRecord::Base
  belongs_to :role
end

class Role < ActiveRecord::Base
  # Defines methods to determine ACL privileges
end

The second is a bit more flexible, but is also slower when trying to resolve a simple yes/no case because more roles must be checked.

class User < ActiveRecord::Base
  has_many :grants
  has_many :roles, :through => :grants
end

class Grant < ActiveRecord::Base
  belongs_to :user
  belongs_to :role
  # Defines methods to determine ACL privileges
end

class Role < ActiveRecord::Base
  # Defines methods used by Grant ACL implementation
end

If you need to have some kind of configuration or meta-data stored about what each role provides for the user, put that into the Grant model as additional fields.

Assuming all the user_types have the same columns/attributes, and assuming you call them 'roles'

table role
 -idRole (int)
 -roleName (varchar X) 
 -description (varchar or text or what have you.)
 -permLevel (int)
 -etc

Then do exactly as suggested and use the idRole as an entry in your user table.

if your roles are more complicated or your assigning multiple permissions levels/type to a single role, you probably want to split out out to an xref type table

if you post what the different user_types/roles are, perhaps someone might point out if they aren't set-up or normalized for how you want to do things in your head. I'd just ask in the comments, but I can't post them yet, sorry.