how to manage more than one site admin and users in database?

Question

I have to design a database for a application which is having millions of users.Now that website will be having multiple administrator like HRadmin,SuperAdmin,SalesAdmin.I can only have one role as Admin.

My concern is that if i handle all user related data in one table,means all users,whether admin or user,credentials will be stored in a single table,searching can be really slow for unique username and other things.

People suggested,have different pages for admin and users,add a keyword to querystring and extract it in code to find the actual admin role.

How should i implement this thing in database so that front end doesnot become bulky.I am using asp.net2.0 and sql server 2005.

Answer 1

Since you're using ASP.NET and SQL Server 2005, have a look at using ASP.NET Membership. There's a tool that will build out the database tables for you, and includes support for multiple users, roles, and profile fields.

Here's an extensive look at ASP.NET membership: http://www.4guysfromrolla.com/articles/120705-1.aspx.

Answer 2

I agree, use an ASP.NET membership provider. You should only need to to do a look-up once when a user logs in - you can then use forms authentication to create a secure cookie that holds the authentication and roles details. So long as you have a well-indexed table, a single lookup should be quick, even if you have millions of rows.

Answer 3

If you will go with ASP.NET Membership - Please also read Performance Considerations for Applications Using Services

Having millions of users will require some optimization, for example we use CacheRolesInCookie as described in the article

Answer 4

Trust me you want only 1 user table especially if you are going to be checking for unique usernames. Proper indexing should make it work correctly. You might also want a related table that defines the roles each person has (they may have multiple roles.)

Answer 5

Putting keywords in the query string is a very very bad idea. If I know what keyword to use to become admin I can fake myself admin rights? If a user is logged in you can place his credentials in the session or cookie (I preffer session, because cookies can be changed on the client side), that might save you a lot of DB requests (I don't know any system that keeps updating your credentials, as far as I know even Windows caches them when you are on an Active Directory).

If you are afraid that looking up usernames in the table will take to long you should optimize your database table. Put an index on the usernames (or use it as a primary key) and you won't experience performance issues searching for usernames (also, when performing many searches on the username SQL server will try to optimize it himself).

Also using different pages might seem to speed things up, but when you need to make changes in your code you have a big problem, because you have to fix the same thing on multiple places. Sometimes re-usability and maintainability should go above speed!