Q:

Database design, which table has the foreign key

I have an Table USER (USER_ID, PASSWORD, NAME, ...) and an Table ACCESS_ROLES for the Users, every user can have one ACCESS_ROLE (ONE-TO-ONE).
Wich table has the Foreign Key?
I would put the USER_ID into ACCESS_ROLES table. Is there any best practice approach?

A:

If you have a fixed set of access roles and any number of users, where a user is assigned one and only one access role, and any number of users can be assigned a given access rule [this is how I interpret your question], then you would put a column like "AccessRoleId" in your USERS table and add a foreign key constraint into ACCESS_ROLES.

A:

Since you will be having a one-to-one relationship, the solution suggested by Philip Kelley is better. Just add a new column in USER called access_role_id and then do the foreign key on the USER table like this:
ALTER TABLE USER ADD CONSTRAINT fk_access_roles_users
                 FOREIGN KEY (access_role_id) 
                 REFERENCES ACCESS_ROLES(access_role_id);

Do not add USER_ID into ACCESS_ROLES table, as you suggested.

