0

For example, we have users of 3 types: internal users (employees of our company), clients, and contractors, and they are all in the same table, with a "type" field to differentiate them. We also have different domains for each: internal.domain.com, clients.domain.com, and contractors.domain.com. With each domain mapping to a user type. In order to log in to these portals, you need to have the correct user/pw obviously, and have the user type match the domain as well. Should these users be split from 1 table into 3?

blacktie24
  • 4,985
  • 6
  • 41
  • 52

1 Answers1

1

You don't need 3 user tables. You're describing the concept of a "role", which would normally result in the following types of tables:

User
Role
User_Role (cross reference to declare which users have which roles)

You would have 3 roles, one for internal, one for clients, one for contractors. This is a normalized design, but you could get by with a denormalized design in a single User table (but I would not recommend it). I assume you will have different logic depending on the role.

Also, I should note that given the types of users and sites you describe above, it's natural to think about doing this sort of authentication using multiple LDAP stores, which - for example - could be implemented with 3 different Active Directories. This allows you to delegate authentication and authorization to IT that controls those domains.

kvista
  • 5,039
  • 1
  • 23
  • 25
  • Hi Kvista, thanks a lot your input and time, I really appreciate it. – blacktie24 Mar 05 '11 at 17:43
  • The first part of your answer makes a lot of sense, defining the "user types" as roles. I don't understand the last paragraph though. Can you elaborate in layman's terms, or provide a link? I've tried searching myself but couldn't find something that I understood. – blacktie24 Mar 05 '11 at 17:55
  • Sure. If you're not familiar with the concept of LDAP for web apps, check out: http://httpd.apache.org/docs/trunk/howto/auth.html to understand how authentication/authorization "providers" can be configured for a portal app. One of these providers can be an LDAP store, like Active Directory: http://stackoverflow.com/questions/663402/what-are-the-differences-between-ldap-and-active-directory. Not an IT expert, but I'm sure you can configure multiple Active Directories across your LAN - again, this is only if you want to delegate admin to IT staff. If you don't need this, go with 1 AD. – kvista Mar 05 '11 at 18:32
  • Thx again for the links, will check them out. Was thinking a bit more on having different user types be set as roles. How would you then assign data specific to users of a certain role? i.e. If I needed to set the office location but only for internal users. So instead of having an office location id field within the users table as a FK to the office locations table, how would I link them? – blacktie24 Mar 05 '11 at 21:27
  • Also, why would having a "userType" field be considered a "denormalized design"? – blacktie24 Mar 06 '11 at 00:52
  • What I meant was that if you only went with *just* a User table and you decided to put the role name (and not, say, the role ID from the Role table), this would be denormalized in the sense that you're referring to a small set of values that *could* be constrained - instead, you're just inlining them in the table and repeating them. In some cases, this would violate 3rd normal form because the role name might not be the actual "key"; but this assumes that you have more info about a role than just its name. (which you would if you have the need to give different users different roles) – kvista Mar 06 '11 at 17:28