The problem:

I have multiple entities which refer to a common entity

Like User, Company etc and they have one-many relationship with Address.

Like user can have multiple addresses and Company can also have multiple addresses.

If I were to design between user and address then i can have userId( as foreign key) in the address table and thus i have user-address as one-many relationship but how do i cater the same with company.

Can someone please suggest the approach to implement such a scenario.

Regards
Hemant

More of a database desaign question than a Hibernate, but what the heck.

Use intermediate mapping tables.

User --> UserAddress <-- Address
Company --> CompanyAddress <-- Address

UserAddress:
INTEGER USER_ADDRESS_ID (PK)
INTEGER USER_ID (FK)
INTEGER ADDRESS_ID (FK)

CompanyAddress:
INTEGER COMPANY_ADDRESS_ID (PK)
INTEGER COMPANY_ID (FK)
INTEGER ADDRESS_ID (FK)

Hi Ananasi,

Thanks a lot mate,

But do we have any other approach like having the entity id and entity type column in the address table.

Entity Id column can hold companyId or UserId and Entity type column can hold "User" or "Company" as varchar.

We have been trying to establish such relationship in the hibernate but of no use.

The approach you suggested looks very good.

But someone in my team pointed that it was have extra joins when we retrieve the addresses of a user or company.

Can you suggest Entity Id /Entity type approach using hibernate

It will be helpful.

Regards
Hemant

Yes, it would involve extra joins in the tables when mapped this way, but to tell the truth, your database server can optimize joins on numeric foreign keys extremely efficiently. The alternative is to use a discriminator column to distinguish between the two types of addresses, as you had suggested with the entity type column. You can see the docs on discriminators here: http://www.hibernate.org/hib_docs/v3/re ... criminator . I would suggest using a numeric column for the discriminator, as the indices over that column will be more efficient, but you have to balance speed with ease of use/clarity.

More info:
Inheritance mapping
Table per subclass inheritance