Hi Dave and Nathan,
Thank you for your replies. In my OpenACS version (5.5) there's an unique index for authority_id or username. However, I can't see any reason for this index to exist, and I'll explain you my reasons.
My understanding from the datamodel was the same as proposed by you Dave: unique index on authority_id and username. However, I've tested a lot of situations and got to the following cases:
1 - User is on external authority and it's not on local authority
On this case, a new user is created on local authority, but the users table shows that the user came from the external authority. This means: there's an entry on the users table (of course, the user has to be created in order to login) that points to the external authority.
We could use an unique index for username here, because the table indicates a single entry in the users table.
2 - User is in local authority but it's not on external authority
The system usually doesn't deal with this situation, because if you choose to login on external authority it'll give you a message saying you don't have an account on external authority. If you choose local authority, the login will happen normally.
However it doesn't seem good to me the fact that user can choose authority. In a regular navigation, users don't care wich domain they're logging in; they just want to access the system. I'm changing acs-authentication (I actually did it already) so you can login only in first authority from the list. If the authority is external and user is not found there, check if the user is local. If it's local, add it to external authority. I've tested it with LDAP and it seems to be working fine. I can provide a patch if more people are interested.
On this case, username can be unique too. The authority_id is going to point to local authority and user will have accounts on all authorities.
3 - User doesn't exist on any authority
On this case, user is added to both authorities, and users table will hold local authority for authority_id.
As you can see, I couldn't find any case where the username is duplicated on users table, but I did found a bug. If it's not a bug, it's a strange system behavior.
Let's suppose you call acs_user::get_by_username to an user wich was created by an external authority. If you provide local authority as a parameter, you will not find it, because it's registered on external authority. It doesn't make any sense, because if an user is registered on the system and have an account on OpenACS, why would we not find it with username? The point is: there's no reason to look by username in an specific authority, because sometimes you will not find the user even if it exists. It seems like a false negative for me.
The idea to add an unique index on username for users table is to guarantee that everytime you look for an specific username you will find it, even if the user is registered in a different authority.
Sorry for the long post, but it seems like a big change, so I would like to hear everybody about it. What do you think?
