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.
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.