Hello !

Our web-application is multi-client capable.
We have a database where every table has a composite-key with a client-number as first field.
Now after fiddle a little bit with a bug in the second-level cache [1], [2] Gavin finally means this is a bad database design.

Now I would like to now why?

What is the alternative to this design?

Should every client have its own scheme in the database?
Then every database change needs to be done in all schemes.

Comment are very welcome.

[1] http://opensource.atlassian.com/project ... se/HB-1280
[2] http://forum.hibernate.org/viewtopic.php?t=935627

--
Mario

Look in the Hibernate Action book about the discussion of putting buisness meaning into primary keys.

Furthermore what Gavin said was:
"A SessionFactory per "company number" sounds like very bad or at least incredibly unusual application design."

And I fully agree - why would you want this ? Why don't you use the same cache for this multiclient app ? They won't collide since they have seperate identies!

What I do see a point on though is if you have colliding identifiers for the same tables in the singleton cache - then you will have a problem (no matter if you have your design or not)

But didn't your patch fix that ?

First of all, yes - I currently read the book - a great work!

This companyNumber or client-id strictly partitions the database.
Thus if the data for one company (client-id) changes due to update/insert there is no need to flush the (second level) cache for the other company.
This is what happens currently if you use only one SessionFactory (or do not use the region_prefix)- the second-level cache is invalidated.

This is why I wanted to create a SessionFactory per companyNumber. IMHO a must have for such a multi-client capable web-application.

And yes, the patch fixes it and a workaround to handle the cache-configuration hell is also possible. But even this workaround is not needet if the cache behaves as documented - every SessionFactory should have its own second-level cache. But I can live with it.

I just want to learn how such a database could be designed better.


I also see problems in NOT using composite-keys if hibernate tries to create its own ids.
I think this is not possible if you use multiple SessionFactories against the same database. For sure, then server generated ids are requied.

Agreed great.

I see problems in not using composite-keys if hibernate tries to create its own ids. I think this is not possible if you use multiple Session Factories against the same database.


phoenix database design professional