Using Hibernate 3.1, PostgreSQL 8.1.
I have a problem with a contact database I am building. Each of the contacts in the contact database may be shared by multiple user groups or may be unique to a specific user group. The number of contacts could be in the hundreds of millions, and most of these will be shared contacts.
The data model has two tables, one with the contacts and a separate table with contact records. (one to many) Each contact record is either shared or specific to a user (really a group of users) If a shared record is modified, it is cloned and becomes a specific record. Although I could map this relationship directly in hibernate, it becomes very complicated to query against. For example, simply querying for all contacts with a first name of "Tim" is not easy since the query has to figure out which record to look at.
In order to hide this complexity from queries, the application, and Hibernate my idea is to create a database view per group of users. Then I would map Hibernate to use that view when retrieving contacts. The problem is that the Contact class mapping actually needs to map against a different view depending upon the user who is logged into the application.
The only way I can think of doing this would be to have multiple SessionFactories - one per group-of-users. Then, based on who the user who is logged in, I would have to retrieve the SessionFactory which maps to the appropriate contacts view.
My questions are:
- Are there any better ideas for tackling the overall problem?
- How will multiple SessionFactories affect Hibernates performance/overhead? They would be sharing a connection pool, so I would imagine this would be minimal.
- How will multiple SessionFactories affect caching? My concern is that most of the database entities are shared between the user groups, so I would want cross-SessionFactory caching to be possible. I'm not quite sure how caching should work for contacts - I would imagine that the contacts would have to have a separate cache per-SessionFactory since retrieving a contact with the same ID by two separate users could return different data. Is there any way to override the way Hibernate determines the name of the cache to use? I guess I could write my own CacheProvider implementation...
Thanks,
Jeremy Haile