ejb3: mapping many-to-many relationship jointable with a simple primary key

Question

often in books, i see that when a many-to-many relationship is translated to a DB schema, the JoinTable gets a compound key consisting of the primary keys of the tables involved in many-to-many relationship. I try to avoid compound keys completely. So i usually create a surrogate key even for the JoinTable and allow the database to fill it up by trigger or whatever feature a database has for incrementing primary keys. It just seems like a much simpler approach.

the only issue i can think is that, there are chances of duplication of foreign key pair in the JoinTable. But this can be avoided by a simple query before a row is inserted in the JoinTable.

Since books always use the compound keys approach, i wanted to know if, there are any negative effects if i use simple one column surrogate keys for the JoinTable?

Answer 1

In my opinion, using a single primary key is a bad idea.

First, as you said, a single primary key won't ensure unicity in the database. Sure, you can check this at runtime using a simple query, but this is costly, and if you forget just one time to do your check query, you can put your database in an inconsistent state.

Also, I think that using an additional column for the primary key is, in this case, not necessary. You don't need to identify relationships by a unique primary key, since the relationship is already defined by two unique keys: the primary keys of your two tables. In this case, you'll have unnecessary data, that will add complexity to your data model, and that you'll probably never use.

Answer 2

I try to avoid compound keys completely.

Huh? Why? I mean, why totally avoiding them? What's the reason behind this?

So i usually create a surrogate key even for the JoinTable and allow the database to fill it up by trigger or whatever feature a database has for incrementing primary keys. It just seems like a much simpler approach.

No offense but we don't have the same definition of simplicity then. I really don't see where it is simpler.

The only issue i can think is that, there are chances of duplication of foreign key pair in the JoinTable. But this can be avoided by a simple query before a row is inserted in the JoinTable.

First of all, don't use a SELECT to check uniqueness (you can have a race condition, a SELECT without locking the whole table won't guarantee anything), use a UNIQUE constraint, that's what UNIQUE is for.

And lets imagine one second that a SELECT would have been possible, do you really find that simpler? In general, people try to avoid hitting the database if possible. They also avoid having to do extra work.

Since books always use the compound keys approach, i wanted to know if, there are any negative effects if i use simple one column surrogate keys for the JoinTable?

So you mean something like this:

   A              A_B                 B
-------      ------------------    --------
ID (PK)      ID (PK),              ID (PK)
             A_ID (FK),
             B_ID (FK),
             UNIQUE(A_ID, B_ID)   

Sure, you could do that (and you could even map it with JPA if you use some kind of trigger or identity column for the ID). But I don't see the point:

1. The above design is just not the standard way to map a (m:n) relation, it's not what people are used to find.
2. A_B is not really an Entity by itself (which is what the model somehow suggests, see #1).
   • The couple (A_ID, B_ID) is a natural candidate for the key, why not using it (and wasting space)?
   • The above design is not simpler, it does introduce more complexity.

To sum up, I don't see any advantage.