Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Modelling complex many to many relationship

 
Rancher
Posts: 187
28
Firefox Browser MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For practice I am messing around with JPA and the MySQL database. I have hit a relationship which I am unsure how to model.

I have customer, order and item entities. A customer owns any number of orders (one-to-many, bidirectional relationship). An order can have any number of items (many-to-many, requires an intermediary table). An item does not have to have an order, or know anything about orders however (unidirectional relationship).

I created a basic model:


All fine and good. Until I realised that a customer needs to be able to order more than one of a given item. With JDBC, I might have placed a 'quantity' column in the intermediary table and set it manually (I don't know if that is a good idea). I have no idea how to model this with JPA (or any ORM mapping) because the quantity is not an attribute of either the item or of the order. How would I model this?
 
Marshal
Posts: 25795
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean like "I want to order 12 cans of corn?" In that case, why isn't there an attribute of order_items for that?
 
Jj Roberts
Rancher
Posts: 187
28
Firefox Browser MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was having difficulty with the JPA side. Are you suggesting that I create an entity for order_items to hold the attribute?
 
Paul Clapham
Marshal
Posts: 25795
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I worked for a wholesale distributor for most of my professional career so it was obvious to me that you should have an "order line item" table which would at least contain the quantity ordered. (Ours had a whole lot of other things too, starting with the unit price of the item.) Sure, it happens to link the order table to the item table but to me that's the least important thing about it.

But we weren't thinking about JPA when we designed those tables, just about business needs. If JPA makes it hard to do that then I'd say that's a point against JPA.
 
Jj Roberts
Rancher
Posts: 187
28
Firefox Browser MySQL Database Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right, my issue is that I was putting the cart before the horse . I shouldn't have been designing with JPA in mind. My db model now looks like this, how I would have done it (with the exception of storing the price of a line item, when you mentioned it I saw that it was a good idea):


Now with regards to JPA, it is clear that I will need a LineItem entity to represent the line_items table, which is easy enough to do
 
Saloon Keeper
Posts: 22479
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:
But we weren't thinking about JPA when we designed those tables, just about business needs. If JPA makes it hard to do that then I'd say that's a point against JPA.



It doesn't. It's one of the commonest things in the world to define a parent/child one-to-many relationship for an invoice, shopping cart, etc. and line items. It's such an essential thing that I often don't adhere to my standard practice of having one DAO per table and let a single DAO handle both the parent and its detail items.

For this, one-to-many on the parent with a reciprocal many-to-one on the line item is all that's necessary and the only hard part is whether to to an eager fetch or a lazy one.

For a practical invoice, you'd often also have a many-to-many relationship between inventory (warehouse) items and line items. Many-to-Many in JPA is a bit of a challenge and best done with a linking table between the two. JPA provides support for that.
 
Police line, do not cross. Well, this tiny ad can go through:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic