Jpa with dynamic scheme

Question

I'm working on a software that uses JSF(2.1)/JPA(Hibernate)/Spring/Jboss7.1. The user will have many clients, and their data wont be used together.

So, I have these tables:

Company
ID | Name
1  | Foo
2  | Bar

Products
ID | Company_ID | Name
1  | 1          | Doll 01
2  | 1          | Doll 02
3  | 2          | Candy

When working on company 1, the user doesn't need the data from Company 2.

Today, all I have to do is use a where clause.

However the DBA thinks we might have problems when the data get really high, and suggests we should use One scheme for each company.

So, the above structure would be something like this:

GeneralScheme.Company
ID | Name
1  | Foo
2  | Bar

Company1.Products           Company2.Products
ID | Name                   ID | Name
1  | Doll 01                3  | Candy
2  | Doll 02

He says, this way would be easier to make specifics backups, and performance could be better as well. But, is it possible to work this way on JPA? Changing the scheme at runtime? And creating new schemes as well since the user might add others companies?

Thanks.

"Premature optimization is the root of all evil." -- Donald Knuth @Topic: I can't imagine how to map an entity to a variety of tables. — Smutje, Aug 15 '14 at 19:22
This is duplicate of similar question http://stackoverflow.com/questions/25041000/in-spring-data-jpa-how-to-add-an-entity-during-runtime/ so see my answer here. — kulatamicuda, Aug 15 '14 at 21:13

Vlad Mihalcea · Answer 1 · 2021-01-13T14:56:37.413

Using multiple schemas as a "horizontal partitioning" mechanism sounds like the "poor man's sharding". I would go with a clean design with only one table: PRODUCT. Since you use PostgreSQL, in case it grows large you can simply partition it.

If table partitioning is too basic you can go and shard even more than one table. Check out how Braintree did it.

Hibernate supports multiple schemas, but mappings are holding the schema info, so loading new mapping at runtime is probably possible, yet tricky.

The best designs are simple and elegant, and if you already start thinking of hacks to support them, you are not there yet.

Hibernate supports operating against multiple databases/schemas/shards in a multitenacy fashion.

score 0 · Answer 2 · answered Aug 15 '14 at 20:03

You could, in theory, use different a DataSource (each with a corresponding EntityManager and TransactionManager) for each Customer schema, but you wont be able to add new Customer schemas without restarting the application (or web server) since you will need to register new beans (new DataSource, etc...) in Spring each time a new Customer is added.

Also you will need to implement discrimination logic somewhere in you service/dao layer so that each customer is persisted using the correct EntityManager instance.

In summary, it is not a good idea, specially since there are other possible database optimizations (depending on you database server).

Obviously, you could do this in plain JDBC.

Jpa with dynamic scheme

2 Answers2