Hi Dave,
I got your point. It seems like a good design on that case. However, let's think about the OpenACS system: the users can authenticate on external authorities as you said. But should they have a single user_id? I can have, as user, a lot of external accounts, such as Facebook, Google Apps, etc, and it's still myself, isn't it? I can't have, for example, two different names (my personal name first_names and last_name) for different authorities. If somebody looks for my data in OpenACS system, it should not be duplicated don't you think?
Maybe the authorities design is ok, but I don't know if it should go to the users table. If it goes to the users table, we should eliminate the foreign key for persons and create a new entity of system users with different login options.
From my understading of OpenACS design (forgive me and fix me if I'm wrong) the users table is for everybody that have a system login. If the same system user can login using a lot of services, this is user can't be identified as an user_id, right? It should be more a party_id or something like this. Imagine the situation where you want to know some user email. The same user could have to entries on users table, because it could have two login ways (LDAP and OpenACS, let's say) but it would be still the same person.
My proposal, and I know it's a huge job, is to separate it. I can see two different ways of doing it: remove foreign key from users table (users_user_id_fk) and identify user by person_id; or remove the fields password, salt, password_question, password_answer, password_changed_date, auth_token, authority_id and username to a new table. This table would have a foreign key for users table (user_id) and there could be duplicated entries for user_id.
Yes, it's a huge job, but it seems like the right way to do it. What do you think?
