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:

PK  ApplicationID

PK  PermissionID

But what's the BEST way to do the join table? I have these two options:

PK  ApplicationPermissionID
CU  ApplicationID
CU  PermissionID


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.

4 Answers 4


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.

  • 2
    Well, you already stated that adding an ID is a minor concession, easily overcome by the potential benefits, so it would seem to me that (given that having a unique ID in every table is more or less best practice in most DBMS's and ORM's) you would advocate having an ID as the "best" or "default" option, rather than not having one. Commented Jan 9, 2013 at 21:11
  • 5
    "You would never need to join or query on such an ID" - saying "never" in a technology situation is inviting that very thing to happen. Saying that, there are times when you will join that join table (yes, I've heard it referred to as a "join" table more than a "junction" table) to yet a fourth table because the joined entities are in fact a business object of their own. Commented Jan 9, 2013 at 21:12
  • 4
    @RobertHarvey. An ID is a good practice for entities. But a junction is more of an implementation detail for many-many relations, not an entity in it's own right. But as Jesse C. slider points out, there are cases were a junction could be considered a business entity.
    – mike30
    Commented Jan 9, 2013 at 21:18
  • 1
    "waste of disk space." - I think some engines (InnoDB?) create an (internal) primary key anyway if you don't make one yourself - so you may not actually gain disk space by not having one.
    – Alex
    Commented Jan 10, 2013 at 18:24
  • @Alex. You put a composite PK on the mapped IDs.
    – mike30
    Commented Jan 10, 2013 at 20:58

Over the years I got into the habit of giving each table "TableName" an auto generated primary key "TableNameID", without any exceptions, not even for junction tables. I can say I never regretted it, because it makes a lot of things easier when creating generic code which does something for "all tables" or "some tables", or for "a lot of rows of several different tables".

For example, if someone asks you to store some rows of different tables (or references to those) in a file or in memory, for example, for logging purposes, it is very handy when you know beforehand that you just need to store exactly one table name & exactly one integer ID, and you don't have to deal with any "special cases".

Another thing, when you start with combined PKs, you will probably some times later run into the need for combined foreign keys (since you may come to a point where you want to add a FK ref to your ApplicationPermissions table). Then the next requirement may be to have this FK to be unique in conjunction with other attributes or foreign keys - which will result in increased complexity overall. Nothing which is not possible to handle for most modern DB systems, of course, but a uniform solution makes life for programmers often a lot easier.

And finally, a statement like SELECT ... FROM TABLE WHERE TableNameID IN (id1,id2,...) does work well with a single column as primary key, but I have never seen an SQL dialect so far which lets you do this with combined keys. If you know beforehand that you will never need a query like this, fine, but don't be surprised if tomorrow you get a requirement which will be solved most easily with this kind of SQL.

Of course, when you expect your ApplicationPermissions table to hold several hundred millions of rows, then you should consider to avoid something like a ApplicationPermissionsID.

  • 1
    Although I didn't end up choosing your answer. I do like aspects of it. Thanks for your thoughts (upvote).
    – solidau
    Commented Jan 10, 2013 at 23:04

While Mike's answer is good, here are the reasons I would add a separate ID field or not.

  1. Consider using a separate ID field for the junction/join table if it contains fields other than the ID. This tends to note that it is a first class entity.

  2. Consider using a separate ID field if APIs or any existing logic tend to use single fields for retrieving/editing entities. That can help other people follow your code in the context of a larger project.

  3. Don't use it if there is no specific benefit (KISS). EF knows how to handle this type of table and a composite unique constraint can sometimes be missed when other people are trying to understand this type of relationship. Also, when normalizing I try to use the smallest key possible that uniquely defines the tuple. In your second example, you effectively have 2 separate candidate primary keys.

table Person
   Id int identity(1,1) not null primary key
   ...other fields go here...
table Address
   Id int identity(1,1) not null primary key
   ...other fields go here...
table PersonAddress
   Id int identity(1,1) not null primary key
   PersonId int not null
   AddressId int not null

Remember to create an index and foreign key on both PersonId and AddressId.

No matter what others think are "better" or "you should", this is the simplest and easiest way to allow the database to function properly.

  • 1
    I think one problem with this approach is the schema allows two PersonAddress rows with identical PersonId and AddressId values.
    – Sam
    Commented Jul 31, 2013 at 3:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.