Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 13 down vote accepted

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.

share|improve this answer
    
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. –  Robert Harvey Jan 9 '13 at 21:11
2  
"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. –  Jesse C. Slicer Jan 9 '13 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 Jan 9 '13 at 21:18
    
"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 Jan 10 '13 at 18:24
    
@Alex. You put a composite PK on the mapped IDs. –  mike30 Jan 10 '13 at 20:58
show 4 more comments

Over the years I got into the habit, when creating a new data model, 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.

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.

share|improve this answer
    
Although I didn't end up choosing your answer. I do like aspects of it. Thanks for your thoughts (upvote). –  Juventus18 Jan 10 '13 at 23:04
add comment
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.

share|improve this answer
    
I think one problem with this approach is the schema allows two PersonAddress rows with identical PersonId and AddressId values. –  Sam Jul 31 '13 at 3:49
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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