[hibernate-dev] Sharing association table for multiple associations between two entity types

[Gunnar Morling]

Hi,

I've two entity types, "User" and "PhoneNumber",with two associations
between them: User#phoneNumbers() and User#alternativePhoneNumbers().

By default, only one mapping table is created for these two:

    create table User_PhoneNumber (
        Parent_id bigint not null,
        phoneNumbers_id bigint not null,
        alternativePhoneNumbers_id bigint not null
    )

At runtime things will fail because of the NOT NULL constraints: Only
one of "phoneNumbers_id" and "alternativePhoneNumbers_id" will be set
for a given association row.

If I remove the constraints (e.g. pretending to work with a legacy
database), things seem to work: association rows are inserted into the
right columns and also are loaded correctly.

My question: Should such sharing of association tables be supported or
not? If yes, we'd have to adapt the generated DDL (making both columns
nullable, and optionally add a check constraint to make sure exactly
one is not null). For map associations, also a primary key is
generated for one of the two associations, so that'd have to be
adapted as well.

If that's not to be supported, can we fail early on, demanding the
user to specify distinct join tables?

I had filed https://hibernate.atlassian.net/browse/HHH-10472 about
this a while ago. But thinking about it again, I realize it actually
could be made work, although it doesn't appear as a desirable mapping
and I'd prefer an early error message.

Any thoughts?

Thanks,

--Gunnar