What kind of Spring Data JPA relationship is this?

[Nathan Milota]

I'm creating an inventory management website application.   There is a warehouse, and inside the warehouse there are bins, and inside the bins there are items.   Inside the bins there can be multiple of the same item.  For example if you are storing toothpaste, you can have 2 toothpaste in one bin, and toothpaste in another bin.   I'm trying to create something where a user can look up an item and find out all of its locations.     This doesn't seem to fit a one to many, because if you put an item in one bin, it won't let you put it in another bin if you have many of the same item.   Is it a many to many?   If so, how do I make it so that if I put one container of toothpaste in bin 12, I can put another one in bin 12 without it causing an error due to the duplicate mapping?  

[Paul Clapham]

It sounds like you have a table which says, for example, "Bin 49 contains 3 cases of Snickers". Wikipedia calls this an associative table, or alternatively a join table or a junction table or a cross-reference table. Sorry, I don't know what Spring calls this but hopefully it uses one of those four names.

[Rob Spoor]

It's a many-to-many, but with an additional column in the join table - the count. The table would have three columns: a foreign key to the bin, a foreign key to the item, and the count. The two foreign keys combined form the primary key.

I don't think this can be done in JPA using a @ManyToMany, because of the additional column. Normally I'd probably go for an @ElementCollection in the bin table, but because you want the relation to be bidirectional (so you can look up bins for an item), you'll have to make it an entity. You'll end up with three entities:

1. Bin. It has a @OneToMany relation to the join table entity.
2. Item. It has a @OneToMany relation to the join table entity.
3. The join table entity. It has @ManyToOne relations to both Bin and Item. You'll need to use @MapsId twice to share the columns for the primary key with the relation fields.

The MapsId documentation (see the link) shows how you can set up the columns in the join table. You'll just have to do it twice, one for each relation.

[Tim Holloway]

Actually, I think you can define a join table with extra fields in it.

What I fiound frustrating about doing many-many in JPA was that - and memory is fuzzy here - JPA was favoring working with a join table that it automatically named itself, as well as naming the join fields and the names didn't fit the standards of the legacy database. So I had to learn how to explicitly set all that up.

Incidentally, SQL may not be the best approach in cases where the relationships between entities are more important that the attributes of the entities. For cases like that, a graph database like Neo4J might be preferable. There is a Spring Data module that supports it. Although the query language can look pretty strange relative to SQL.

[Rob Spoor]

Tim Holloway wrote:Actually, I think you can define a join table with extra fields in it.

Then would that be done with a @ManyToMany on a Map? The key would then be the entity, the value the single column or an embeddable.

What I fiound frustrating about doing many-many in JPA was that - and memory is fuzzy here - JPA was favoring working with a join table that it automatically named itself, as well as naming the join fields and the names didn't fit the standards of the legacy database. So I had to learn how to explicitly set all that up.

True, the default (if I recall correctly) is <OwnerTable>_<OtherTable> and <ownerId> / <otherId>. @JoinTable can be used to tweak that.

Incidentally, SQL may not be the best approach in cases where the relationships between entities are more important that the attributes of the entities. For cases like that, a graph database like Neo4J might be preferable.

Agreed. People are used to SQL, but there are situations where fitting models in SQL is just not the correct way.

[Tim Holloway]

I'm pretty sure I have an example of what I'm proposing in a project somewhere, but alas, I cannot easily locate it. Nor, unfortunately, my venerable aPress JPA2 reference, which might illustrate such a concept.

The primary problem as I noww recall isn't so much the name of the join table - which can be overridden on the annotation - but on its definition and maintenance. A basic join table requires no application logic because JSF understands everything that should be done with it and does it automatically.

But I think that you can define your join table as @Entity and thereby make it available for additional payloads and operations. Although I'm unsure as to whether doing so would be blessed in all JPA versions and implementations. It's possible that being both an @Entity and a @Jointable might offend some implementations.

Obviously you can define an ad hoc manual Join Table as an Entity by simply making its primary key be the concatenation of the two (or more) foreign keys that reference a row in the table and doing many-to-one references from the tables that join on it. You don't get the full automation of a native @JoinTable, but it does give the join attributes.