Question

Getting a few projects started with EF, but I had some questions about join tables and keys etc. Lets say I have a table of applications and a table of permissions. Applications have many permissions and each permission can belong to many applications (many-to-many).

Now, the Application and Permission tables are easy:

Applications
--------------
PK ApplicationID
    Name

Permissions
--------------
PK PermissionID
    Name
But what's the BEST way to do the join table? I have these two options:

ApplicationPermissions
-----------------------
PK ApplicationPermissionID
CU ApplicationID
CU PermissionID
OR

ApplicationPermissions
-----------------------
CPK ApplicationID
CPK PermissionID

PK = Primary Key
CPK = Composite Primary Key
CU = Composite Unique Index
Have you ever been burned doing it one way over the other? is it strictly preference? It has occurred to me that a lot of the "differences" will be abstracted away by my repository pattern (for example, i would almost never create an entire permission object and add it to an application, but do it by ID or unique name or something), but I guess I'm looking for horror stories, one way or the other.

Answer 1

I believe you mean "junction" table, not "join" table.

There is no need for a junction table to have it's own ID field. You would never need to join or filter on such an ID. You would only join or filter on the ID's of the tables you're mapping. An ID on a junction table is a waste of disk space.

So the "best" option is to avoid the ID. Typically a junction table will have 2 covering indexes. Each covering index using one of the mapped ID's as the primary sort field.

But "best" is not by a long shot. It's a very minor issue to have a redundant ID field. You will not have any horror stories over a small amount of wasted disk. The ID won't "steal" the clustered index because you don't want to cluster on the mapped combo anyway.

If your framework wants all tables to have an ID, then go for it. If your team's database standards dictate all tables must have an ID then go for it. If not, then avoid it.