Modeling "relationship attributes" with EJB3 entities

[lhoriman]

How can relationship attributes be modeled with EJB3 entities? A simple example is a database that stores movie ratings.

The main entities are Person and Movie. Each person can rate a movie.

In the database, you would naturally have a Rating table that has columns {person_id, movie_id, rating}. This suggests an EJB3 entity Rating with a composite primary key of {Person, Movie} and a single field, the rating value.

I've tried this, but it doesn't seem to work (an exception when hibernate is processing the annotations). Is this a bug? Or is there a better way of modeling this scenario? It seems like it should be pretty common.

I'm running jboss-head.

Thanks,
Jeff

[lhoriman]

To answer my own question...

Most of the important stuff is spelled out here:
http://www.jboss.org/index.html?module=bb&op=viewtopic&t=63002

Basically:

* Use a composite PK for Rating which has the raw Long values for personId and movieId.
* In addition, include the ManyToOne relationships in Rating, not as part of the PK, and make them insertable=false and updatable=false. Make sure they use the same JoinColumn as the columns in hthe composite PK.

Seems to work fine, and it's more convenient to create the simpler PK when doing lookups.

Jeff

[epbernard]

if you can use a surrogate key, you won't have to use a composite pk

[lhoriman]

"epbernard" wrote:
if you can use a surrogate key, you won't have to use a composite pk

I wouldn't be able to do cached lookups on the real-world key (ie, {PersonId,MovieId}.

I'm actually pretty happy with the existing solution.

Jeff