Is there a canonical way of dealing with a parent entity that has multiple collections of the same child entity?

e.g.


I've tried using a property (backed by an enum in the Car class) to sort them, but this is failing because both collections are trying to add a duplicate AFICIONADO_ID column to the CAR table.



Is there a canonical way to go about this? Is the best way just to suck it up and have two foreign key columns in CAR?

Hi,

as you did not report how you mapped class Car,
it is not completely clear to me whether you use/want bidirectional or unidirectional relationship.



This suggests me that you want to have the relationship bidirectional.
Well in this case, that what you intend to do currently is not mappable as it goes against Uml-specification,
you are currently trying to do something like this

+----------------+ +---------------------+
| AFICIO |---\ | CAR |
| | >------------- | af_id |
| |---/ | |
+----------------+ +---------------------+

But you cannot start from Aficio with 2 relations and arrive to Car just with one.
You cannot 'share' the same field CAR.AFICIONADO_ID for 2 different relations!


Solution:

Alternative1: model the 2 relations unidirectional from Aficio to Car (specifying 2 different secondary table names !)

+----------------+ +---------------------+
| AFICIO |------------------->| |
| | | CAR |
| |-------------------->| |
+----------------+ +---------------------+

Alternative2: use different key column names in order that Car has 2 different foreign keys,
in this case you can remove the where-clause

+----------------+ +---------------------+
| AFICIO |---------------------| af_id_rent |
| | | CAR |
| |--------------------- | af_id_own |
+----------------+ +---------------------+