Database design, which table has the foreign key

Question

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?

Answer 1

If you have a One-To-One mapping, I would put the foreign key into the table that you will be querying most of the time.

Answer 2

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.

Answer 3

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.

Answer 4

Not sure I understand the question or not: I think the access_table should have the Foreign Key.

Answer 5

• If every USER has exactly (or at most) one ACCESS_ROLE.
• One ACCESS_ROLE can have multiple USERs

Then:

• FK parent is ACCESS_ROLE and links to the PK of the ACCESS_ROLES table
• FK child is USERS and the FK column is the ACCESS_ROLE column

Note: the foreign key "parent" column(s) must have be constrainted unique. If you have multiple users per ACCESSROLE, the FK must be from USERS to ACCESSROLES

In SQL Server

ALTER TABLE USERS WITH CHECK ADD
CONSTRAINT FK_USERS_ACCESS_ROLES FOREIGN KEY (ACCESS_ROLE) REFERENCES ACCESS_ROLES (ACCESS_ROLE /*PK?*/)

Answer 6

You can add a foreign key to ACCESS_ROLES

and the following to enforce one to one mapping

UNIQUE (ACCESS_ROLES_ID, USER_ID)