How to map many-to-many association to a class mapped to two different tables?

Question

I have a Voucher - POJO mapped to two tables. The first mapping assigns an entity name "voucherA" and maps the POJO to TableA. The second mapping uses "voucherB" as entity name and maps the POJO to TableB.

Now i have also a customer POJO mapped to TableC. This POJO references vouchers in a list.

<list name="vouchers" table="TableC_vouchers">
  <key column="pid"/>
  <list-index column="position" base="0"/>

  <!-- how to do that right -->
  <many-to-many column="voucher_id" entity-name="voucherB"/>
</list>

How do i properly map a list of many-to-many associations from customers to vouchers so that if a customer POJO is persisted, the Voucher entities are persisted to TableB if they don't exist there, instead of TableA? Can this be done? If not, what would a workaround look like so that vouchers used by customers are persisted to tableB? (TableA contains only available Vouchers, not the used ones)

Answer 1

My suggestion would be to store all vouchers in the same table. To distinguish between used and unused ones you could either have a boolean flag or a discriminator value (if you're using inheritance in your Java code).

Even if you have existing data it does not seem like the migration would be terribly difficult. Once all vouchers are in the same table, their relationship to customers becomes a straight-forward many-to-many.

I think maintaining two tables would be difficult. Essentially, you're still storing whether a voucher is used or not, but you're not doing it explicitly. I'm sure there could be a workaround, but I think what I've outlined above is much simpler. In my experience, this is the route I have chosen every time when faced with a similar problem.

Answer 2

Your core model seems wrong. Your Voucher entity presumably has many attributes - do ALL of them change after it's used by a Customer? I doubt that. And yet, you're duplicating them in your A and B tables which means your schema is not normalized.

"Available" voucher and "used" voucher are not (or should not be) the same entity. I would instead suggest that you create a new entity for UsedVoucher that would link to both Voucher as many-to-one and Customer as many-to-one and contain only "changed" properties of Voucher (if any). So,

Voucher(id, other attributes) // doesn't change from what you have now
Customer (id, other attributes) // doesn't change except for many-to-many; see below
UsedVoucher(id,
 voucher, // what Voucher was used by that customer
 customer, // what Customer has used that voucher
 changed voucher attributes, // if any
 additional attributes // if needed, such as date/time when voucher was used
)

Your "many-to-many" on Customer will become "one-to-many" (collections of Vouchers used by this customer) IF you need it as maintainable property; otherwise it's easily retrievable via query.

You can't physically delete from Vouchers table under this scenario, though (unless Voucher in question was never used). You'll have to do a logical delete instead.