This week's book giveaway is in the Kotlin forum.
We're giving away four copies of Kotlin for Android App Development and have Peter Sommerhoff on-line!
See this thread for details.
Win a copy of Kotlin for Android App Development this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Single mapping thousand schemas at Hibernate: Suitable and how to?  RSS feed

Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everybody.

I'm doing prospection about what's the better aproach for my case. Basically what I need to know is a) how to switch between schemas using Hibernate depending upon the user and b) if this is a suitable approach in order to achieve performance and scalability. When I say user I say a) one that is logged into the application and b) one which has its own DB schema. All the schemas will share all of their entity-relation structure, but of course, different data. My prospected DB (Postgresql) is seem to be enabled to support that thousand schemas transparently.

Looking around, I found info about how to do this by defining multiple SessionFactory, but I think this approach don't work well for me as I need schema availability as soon as the related schema/DB structure is created from scratch (through a DB script), nor compile-time SessionFactory definitions inside an XML file, nor JNDI static schema binding.

I looked around internet, and several people suggest, in cases like this, not to do so; otherwise, they suggest to maintain all the info into the same schema. I think this possibly will not scale well for me, because a) it will lead to put too much relations between the entity that represents my user/business unit and all the other tables which may have a relation to the user, and it will complex my queries, b) the information from user1 isn't necessarily related to the info of user2, c) I expect a very fast growing (possibly exponentially) of the information and the users (possibly hundreds to thousands users) with thousands of rows of information there, as my solution is basically a cloud-based inventory management system; so a single schema will rapidly become overwhelmed and surpassed, and it will lead me to a table partition/clustering management nightmare. I see a divide-and-conquer node/schema approach may scale better (I could be wrong, anyway).

I think possibly this propossed approach may not be suitable considering there are the cache features and the info may eventually collide when another schema is loaded; any caveat/suggestion on this?

Hope you may help me to clarify my requirements in this case, and of course, how to switch between schemas by using the same application entities.

Thanks in advance.
Posts: 11338
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My first question with such a scenario is a very easy one: why? Why would your application need a schema for every user? Why can't they share the same schema and tables?
I didn't do it. You can't prove it. Nobody saw me. The sheep are lying! This tiny ad is my witness!
Programmatically Create PDF Using Free Spire.PDF with Java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!