I have my tables as below
Role(Role ID, Name, Other_Columns)
Command(Command ID, Name, Other_Columns)
I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName)
instead of RoleCommand(Role ID, Command ID)
. I feel like it is easier to read or populate the association table in this case if the names are used for reference. Please note, RoleCommand
is populated manually with insert queries. There is no Web UI. I will setup unique key constraints and foreign key references on Name
columns. Does anyone use this kind of references in production ? Most of the times I see only IDs being used for association tables.
UPDATE:
I am considering using name
for foreign key references because the insert queries for RoleCommand
are so ugly with nested SELECT
queries inside VALUES
to get the ID values from names. I don't want to just use IDs in the insert queries, to avoid errors.