Surrogate key in 'User' / 'Role' tables for desktop app? Whats the purpose?

Question

I have to add some security for a C#/.NET WinForms/Desktop application. I am using Oracle DB back-end.

The tables are simple: User (ID,Name), Role(ID,Role), UserRole(UserID,RoleID).

I am using the windows account name to populate User table. Role table will for now just be simply 'Admin','SuperUser','BasicUser'...

Since no two people could ever possible have the same windows account name... even when I do not control these name management (netops does, hence why I want to use windows accounts so I don't have to manage it ;)). For Role table, I should again never have dupe value - I control the input, there will only be 3 (tactical app going away within year). UserRole is a join table to represent the Many-To-Many relationships of users and roles, so no surragate key is justified.

Simple question - Why bother with 'ID' (int) in the User and Role table? Any point or advantage here? Is this one of those 'I've always done it this way' type things? Or have I just not done this in awhile and forget the reason?

Answer 1

Names change - primary key values must not. Abigail Smith becomes Abigail Jones and the username changes but a surrogate key protects against having to cascade those changes everywhere.

If you are using a surrogate key but there is a column or combination of columns which should be unique, then enforce that using a unique index. There's a good chance you'll want indexes on your user.name and role.role columns anyway, and a unique index is more space efficient and supplies useful metadata to the optimizer. If you have a surrogate key but don't have another combination of columns that uniquely identify a row then think again whether you have your entity definition right.

One caution. Especially for very narrow tables with few access paths, you may use an index-organized table. Oracle will only allow an index organized table on the primary key, but does allow foreign keys against a unique set of columns (if it is enforced by a unique constraint, not simply a unique index).

It is possible that you'll end up with a table where a unique ID is enforced through a unique index and treated as PK by an ORM and used as the parent for foreign key relationships, but the primary key (as defined in the DB) is the rolename/username/whatever because you want that as the driver for an index-organised table.

Answer 2

A surrogate key is not required on intersection tables, but here are a few reasons to do so:

• Consistency: If every table has a single artificial key, you always know the key name when you know the table name.
• Ease Of Use: Less typing — one key means ON and WHERE clauses are shorter and thus less error-prone.
• Interoperability: Some ORMs only work well with tables with a single primary key column.