Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a database design question. Basically I want to be able to create a schema for a User model, then use this User model in other models that extend User but I want to design it in such a way that it's generic enough to be used in every application.

For example a Profile or Account model might extend User, and in both cases they will be different based on the web application you are designing but the core credentials of User should never be different across any web application.

What fields do you think should be in the User model?

I think the bare minimum to successfully handle authentication would be:

  • email (the login unique identifier)
  • salt (obviously!)
  • password (duh)
  • lostToken (a hash to verify lost password functionality)
  • role (member, admin, editor, etc.. IMO this list of roles would differ between sites however it's too important to the User model not to have here?)

Now we get into other interesting fields that are still very very useful:

  • createdAt (when the account was created)
  • ipAddress (track the ip when the account was created)
  • refererUrl (which site it came from)
  • lastLoggedIn (the last time the user logged in)
  • isOnline (is the user currently online)

And even more fields that are still pretty useful:

  • username (might not be used on every site)
  • number of consecutive logins (similar to the stack network)

I think anything else like social data (likes, votes, profile views), badges/achievements, the last time they updated their profile/account/whatever, and other info like their name belong in the per-site Profile model.

What do you think?

Edit: I fully understand that this question is partly subjective but I do think there's definitely room for discussion.

share|improve this question
    
This is an interesting topic and perhaps there's room for discussion, but since this site encourages objective answers, do you think this question can have a single, objective answer? –  FrustratedWithFormsDesigner Mar 14 '12 at 15:33
    
It's debatable and honestly I've only used stack overflow to post objective programming language specific questions. I figured this exchange site would be for questions that's not necessarily programming language specific. I think if a few people replied and a few more people voted up responses a single objective answer could be found. –  AntelopeSalad Mar 14 '12 at 15:40
    
If the question was more technical and less subjective, it might be good over here: dba.stackexchange.com/questions –  FrustratedWithFormsDesigner Mar 14 '12 at 15:45
    
Would it not belong on stackoverflow too? I just searched there for database related questions and there's a ton of "how should i design my database" questions that are likely subjective. –  AntelopeSalad Mar 14 '12 at 15:50
1  
Feel free to close it then, I'll re-ask the question in another way I guess. –  AntelopeSalad Mar 14 '12 at 16:40

2 Answers 2

My first instinct would be to reuse an existing user authorization mechanism, rather than writing one. Then I would skip the rest until I had firm requirements. Premature generalization is just as bad as premature optimization.

share|improve this answer
    
I am using a node/express/mongodb[mongoose] stack while using passort to handle authentication. It controls local authentication and third party sites like twitter, facebook, etc. too and doesn't care how your db is setup or what db you even use. I'm certainly not rolling my own auth from scratch. However with the stack I'm using, it does allow me to define a schema for any models I wind up creating. –  AntelopeSalad Mar 14 '12 at 15:53

I'd advise for a separate database, we use STS and SQL Server for our 'User Store' schema. It roughly contains:

  • User Table - This contains UserID, Password hash etc. It also has a ID field which is an indentifier for the user other than the User ID.
  • User Audit - This contains audit information everytime the user authenticates, URL, IP Address, etc.
  • Application - Known applications in the system
  • Roles - A role is a lists of policies
  • Policy - A specific policies
  • Environment - Defined Environments (SQL Server DB)
  • License (We rolled our own), this probably is not in your schema

When a user authenticates, as part of that process we need to know what application and what environment they are using/going to. A user could have multiple environments for one applicatoin (Dev, Test, Integration, Production), so there is that level of separtion. Once we know that, then we look up there role and policy set for that appplication/environment and store that in the token. At that point the user has authenticated and we know that user via thier ID. They also have thier policy set readily available for any application specific logic since wherever they go, the token follows. The UserID and password are out of the picture and the application no longer needs or uses it, and now all references are by the ID, not UserID.

It's generic in the sense that if another team develops an application all they have to define is there application, roles, and policies and then they can use the STS that is set up. Since we have a common ID to identify the user, the application itself can have further table(s) defined for any application specific attributes as needed.

For example, badges and achievements would not be stored in the user store, rather they would be stored in the application schema and would be referenced by the ID of that user.

Basically, the user store is generic when it authenticates only. Additionally, we have extended it to allow for applications to hold authorization information as well.

By doing that, we can create an administration console for any number of applications that can control user authentication and authorization. Any user attributes beyond authentication and possibly authorization need to be stored elsewhere.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.