conflicting user management design

Question

I'm building a user notification system on a website which involves 2 levels of registration: admin and client

I have all users register into a single registration table with the fields:

uid 
email 
password
owner 
cid 
admin

When an admin level user is inserted, the admin column is flagged with a 1 When a client level user is inserted, the admin column is 0, the owner is set to admin's uid admin level users cannot modify the client user's password the client level user can belong to multiple admins multiple admins can share the same client level users so it is possible for other admins to have clients who are admins

Problem My problem is when an admin user creates their client users, the client user may enjoy the site and might like to register as admin and setup potentially the same duplicate users with the same emails with different passwords. If this happens, the client becomes an admin, then there is the issue regarding whether the admin is still a client of the previous admin.

Example: Bob registers as admin and setup up Jack as a client user. Jack uses the site and likes it. He turns around and registers as an admin account user and puts Bob as his client user.

here the problem is you have emails with 2 diff passwords which leads to a login conflict

Obviously this is a bad design from the get go and can easily be solved with a unique username/password combo; however; I would like to find out what other suggestions the community might have while keeping the email address as the username in this situation.

final goal My final objective with this problem is I'm trying to figure out how social network sites not only manage their users but also how they establish "friendships" as well.

thanks in advance.

Answer 1

The simplest solution is to add another layer in authenticating users: user-type.

When a person logs in they are presented with the following:

1. A drop down mneu with the two user type options (User, Admin)
2. A username field
3. A password field

A user is only considered to be successfully authenticated when all three fields match what is in the database. And this way you know what functionality to present based on the user type.

Answer 2

Why can't you put a unique constraint on the email? This would ensure that a user couldn't be created more than once with the same email.

The when you're creating a new user you just need to do a quick db check for the email address.

SELECT uid from users where email = '$email';
if (userexists) {
 //update admin = 1 where uid = $uid;
} else {
 //insert
}

Appoligies for the pseudo code - I'm primarily php and you didn't mention your language.

This of course assumes that this is a new system and you don't currently have duplicate emails (it won't let you put the constraint on if you do). However you could write a script easily enough to get duplicates

SELECT email, count(*) as dupes FROM users GROUP BY email HAVING dupes > 1;

Then i would just use the highest level the person has and remove the duplicates.

One of the benefits to this approach is that you'll maintain the owner and will be able to track that so and so made x a client who is now an admin and has y clients so clients y are a result of x originally.

If there are different pages based on the user level what i've done in the past is like Ankur said is have a drop down for either login. However I would just check if admin == 1 for admin and admin <= 1 for client.

Answer 3

There's some gaps in the requirements:

• can a client belong to more than one admin - I'll assume the answer is yes
• can an admin modify a client's info (password, etc.) - I'll assume the answer is no

I think the table design has a few flaws

• owner/admin fields play the same role - both indicating hierarchy
• there's no indicator if they've been invited to be a 'client' or by whom

I would recommend two tables: User table

• userid
• password
• date joined

UserRelation

• userid
• client_id (the userid of the person treated as a client)
• client_active (if the client accepted the invite)
• date_invited
• date_accepted
• status (active/not-active)

so, there's a 1 to many between User and UserRelation table. You can determine if the user is an 'admin' if they have accepted client's, the user does not have to register more then once to be a client and a user can be assoc. with many admins...(there's a few other benefits also)