How to persist an Entity that has a relationship with Another Entity from another schema?

This question is a bit complicated for me to explain. So I will divide it in parts and try to draw some pictures.

Most questions that I’ve read before posting, especially on stackoverflow does not contemplate a certain subject, which is about relating a Class ‘A’ from Schema S’ to a Class ‘B’ from Schema X’


Current Configuration

  • Mysql
  • Java
  • Hibernate

Explanation

I have 1 schema (call it S’) that contains tables which contains information that is accessed by several companies. Those companies can only Write/Alter their own defined Schemas. However, They access schema S’ to, for example, get a list of all users who can log-in to the system and are partners (which is done with a SQL query).

Therefore, I have a class on my app called User. This User class DOES NOT/SHOULD NOT exist in each company schema.

The picture below tries to illustrate the situation:
Illustration of the "brief explanation"

According to a few solutions in stackoverflow, a way to do this would be to have two different SessionFactory objects. One pointing to this shared schema and the other pointing to the schema that is owned by the company.

Problem

Suppose I have a Class called Book and the class User mentioned above, as such:

@Entity
public class Book {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

private String name;

@OneToOne
private User owner;

...

}

@Entity
@Table(schema = "S")
public Class User {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

private String username;
private String dateOfBirth;

....

}

If I follow the suggested solution, using two different SessionFactory, how can I persist class “Book” when it has a relationship with “User” ? The EntityManager wont persist the Object “User” that exists on the class “Book” because it wont be mapped on its SessionFactory.

if SessionFactory s1 is mapped to the shared schema “S” and SessionFactory s2 is mapped to the current company schema:

  • If i use the EntityManger from s1, it wont persist the class
    “Book” since its not mapped.

  • If I get an EntityManager from s2, the manager wont persist the
    User Object.

What should I do to persist the class Book when it has a @OneToOne relationship to class User if the table Book is in schema X and the table User is in Schema ‘S’

Answer to friends :smiley:

  1. Are all the schemas for each company including S` stored in the same database instance or do you use multiple MySQL database instances?

Yes. Everything on the same database.

  1. How does the application know which company schema to use? Is that configured as a part of the application’s setup and you simply run multiple application instances where each instance is assigned to a given company or do you use something else?

This is how it works:
Every client/node has a .jar. When he/she opens the app, it connects to schema S’ asking for username and password. The Schema S’ has a table with the following columns : id, username, password, uniqueCompany (FK). This FK, is in a table on Schema S’ (as well) with the following columns: id, db_url, db_password, … and so on)

So basically, if the login is a success, the query* will return the database info that the user is supposed to use after that.

Since there are 1.n companies, there are several schemas.


To make it clear, the application knows which schema to use when the login is successfull.
If my english is a bit complicated, I can draw an illustration. But thanks a lot for the reply. :smiley:

You cannot persist entities that contain associations which span differing SessionFactory instances. However, if you can find a way to use a single SessionFactory, then its possible.

There are a couple things you didn’t touch on in your question that I need to get a bit of clarity with.

  1. Are all the schemas for each company including S` stored in the same database instance or do you use multiple MySQL database instances?

  2. How does the application know which company schema to use? Is that configured as a part of the application’s setup and you simply run multiple application instances where each instance is assigned to a given company or do you use something else?

Thanks a lot for the reply, im really happy you answered. I edited my topic, let me know if that helps you help me :smiley:

Right now it would seem you have a SessionFactory that your login phase uses to interact with the S’ schema in order to authenticate the user and then obtain the company FK along with its database credentials to move forward if the user is validated. I would then assume your application maintains a cache of created SessionFactory instances per company FK and attempts to reuse it rather than you actually spinning up a new SessionFactory per login, which is wasteful in many ways.

That would work and I understand your goals but I wonder if there is a better, more decoupled path. I would actually sit back and look at this from a micro-service and multi-tenant pattern.

Authentication

Lets actually take authentication and decouple it. The goal here is to design a micro-service that your application will redirect to if no authentication token is present in the session or if the token present is no longer considered valid for any reason. This authentication service will also store a tenant-id, or what you call the company FK (or unique id) in the session which is critical later.

Application

In the application, we’ll use Hibernate’s built-in multi-tenancy support using SCHEMA separation. What this means is we’ll build a set of common entity mappings and Hibernate will assume that each schema that is configured per tenant-id has exactly the same database structure with entities. The purpose of the tenant identifier is to drive which schema the application uses.

To drive that from the tenant identifier, you’ll need to implement the following two interfaces:

  1. MultiTenantConnectionProvider
  2. CurrentTenantIdentifierResolver

And then you’ll need to supply the necessary configuration to hibernate:

  1. hibernate.multiTenancy=SCHEMA
  2. hibernate.multi_tenant_connection_provider=...
  3. hibernate.tenant_identifier_resolver=...

Associations to User entity

Duplicate the data.

One of the pivoting points of micro-services is to try and keep things for a service self contained. You want to expose only what is necessary in such a way that the service can grow and change in such a way that it has minimal to no impact globally on your infrastructure’s landscape.

In your current design, if you actually have your application point to the tables in the S` schema, then what happens is anytime a table change to the authentication schema happens, you’ll likely need to deploy a new application version, that shouldn’t be the case.

The idea behind duplicating the data solves that but it also opens the door to allow the application to store the user data in potentially a different way that is better suited for the types of queries that the application will perform.

There are many ways to duplicate the data across schemas, each with their own pros and cons. You can obviously write it using triggers or other database concepts, but you can also accomplish it using automated tools like Debezium or Apache Camel to name a few.

One obvious benefit I see to replicating the data is you can apply filters and isolate which users are a part of each company schema’s user table based on the tenant-id mapping.

In other words, users that are associated with Company A are only in that company’s user table in its schema, users associated with Company B would be all that is stored in theirs, and so on. There wouldn’t be this global exposure of all users as your picture currently illustrates at all.


All this may see like overkill to what you were doing prior, but it has the potential to scale very well in the future. It opens the door to allow you to leverage single sign-on across multiple applications, it allows for better and more secure separation of data on tried, trusted, and true methodologies that require minimal custom custom from the developer’s perspective.

Your question is actually a bit ironic in that this is precisely in part the type of infrastructure and design that my prior life used before I joined the Hibernate team several years ago. If anything, I hope this sheds some light on all the unique possibilities out there and decoupling things into shared components can have strong impacts on future development.


Edit:
One aspect I probably failed to make clear is that your User entity mappings in the company schemas should likely be marked as @Immutable. These tables are read-only by the application. This means that if there is changes that should be made to the user that need to be reflected in the authentication schema S’, the changes should originate there and flow back down into the individual company schemas asynchronously.

1 Like

Thanks alot Naros!! I will study your answer and reply soon.
Just a heads-up. My app is a desktop application. So I do need to re-create 1 SessionFactory per login.

In that case, well, since its a desktop-application. Does your solution with Tenants, still apply?