MySQL Unique Identifier

We are trying to develop a system where a person creates an account where the username is the person's email address. The problem is, the person can have his own unique account (where he is the admin), plus be a "user" of someone else's account.

The "admin" of an account would be able to assign a person's email address to their account plus create a password for that person to log in as a user.

In MySQL, we are having a problem trying to identify what account to log the person into since the email address is the unique identifier. Is there a better way of doi开发者_JAVA技巧ng this?

Obviously, using unique usernames for each account a person was assigned to would solve this issue but we were hoping to use one email address since it would be less to remember for a user.

Any help would be appreciated!

Separation of Concerns principle applies, Separate the two pieces of functionality, (Logging in, and accessing an account) so that they are separate independant functions....
A User logs in, (email and password simply authenticates that he/she is who they say they are). It is not associated with a single account... Separately, associate each account with those users who are allowed to access it. (In Database, this will be many-to-many table) Then, if the user is an admin and has access rights to more than one account, then ask him/her which account he/she wishes to access.

This has added benefit that if you have auditing needs, you will be able to record, for auditing purposes, not just which account was being used to perform any business functionality, but the actual user who performed the function...

You need to give the user a way to tell which account he wants to login to. You could for example have a dropdown next to the login form, where the user can choose between login as an admin or as a guest.

Otherwise, you could have a second screen after the login form where the user chooses the two options.

In any case, if you don't use two separate username (or passwords, by the way), you cannot know which account the user wants to login to,

The "admin" of an account would be able to assign a person's email address to their account plus create a password for that person to log in as a user.

In this case i think it would be better to move the users to another table says account_users and associate with the persons table through the primary key id or username in your case. This way one admin can create many users, even if any of the user is an existing admin. Dont know if this would be the best way though ?

A common method in determining which account to login under is to have account subdomains. This works particularly well for "company centric" web apps.

In other words, if a user has an account for Company XYZ, their account address could be companyxyz.webapp.com. The subdomain could be auto-generated or chosen at signup. With account subdomains, user logins only need to be unique within a given account subdomain.

If there are 2 accounts for the same user (e-mail address) ask which one he wants to be logged in. Just an idea...