Share tenant data across schemas in multi-tenant PostgreSQL

Question

I'm currently working on an application that makes use of PostgreSQL schemas for multi-tenancy (1 schema per tenant).

Now I encountered the following problem: I want to establish relations between entries from different schemas (So that tenants can use some data from other tenants).

I made a small sample project to explain this better:

/*
    sample project goal
    ----------------------

    one school per schema

    every school has students
    every school has assignments

    You can assign assignments to students. Those assignments can be from every school.
*/

/* reset */

DROP SCHEMA school1 CASCADE;
DROP SCHEMA school2 CASCADE;

/* create schemas */

CREATE SCHEMA school1;

CREATE SCHEMA school2;

/* create student tables */
CREATE TABLE school1.students(
    id serial primary key NOT NULL,
    name varchar NOT NULL,
    unique(name)
);

CREATE TABLE school2.students(
    id serial primary key NOT NULL,
    name varchar NOT NULL,
    unique(name)
);

/* fill student tables with sample data */
INSERT INTO school1.students ("name")
VALUES ('Max');
INSERT INTO school1.students ("name")
VALUES ('Sarah');
INSERT INTO school1.students ("name")
VALUES ('Jane');

INSERT INTO school2.students ("name")
VALUES ('David');
INSERT INTO school2.students ("name")
VALUES ('Lisa');
INSERT INTO school2.students ("name")
VALUES ('James');


/* create assignments tables */
CREATE TABLE school1.assignments(
    id serial primary key NOT NULL,
    title varchar NOT NULL,
    unique(title)
);

CREATE TABLE school2.assignments(
    id serial primary key NOT NULL,
    title varchar NOT NULL,
    unique(title)
);

/* fill assignment tables with sample data */
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 01');
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 02');
INSERT INTO school1.assignments ("title")
VALUES ('Assignment 03');

INSERT INTO school2.assignments ("title")
VALUES ('Assignment 04');
INSERT INTO school2.assignments ("title")
VALUES ('Assignment 05');
INSERT INTO school2.assignments ("title")
VALUES ('Assignment 06');


/* create assignments_students tables */
CREATE TABLE school1.assignments_students(
    student_id int REFERENCES school1.students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    assignment_id int REFERENCES school1.assignments (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT assignments_products_pkey PRIMARY KEY (assignment_id, student_id)
);

CREATE TABLE school2.assignments_students(
    student_id int REFERENCES school2.students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    assignment_id int REFERENCES school2.assignments (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT assignments_products_pkey PRIMARY KEY (assignment_id, student_id)
);

In this example application, students can only be assigned assignments from the same school. What I want is that schools can 'share' some of their assignments so that students from other schools can be assigned to those assignments ('Max' is assigned to 'Assignment 01' and 'Assignment 04').

When I want to create foreign keys on the many-to-many table though, I have to assign a specific schema (REFERENCES school1.students (id)). That's not what I want since the assignment can potentially come from any other school (shema).

How would I go about implementing this functionality here?

Saravanan Saravanan · Accepted Answer · 2018-12-22T18:11:28

There are a few ways of handling this problem

Approach 1

Create a master database of the assignments (entity) and have a table that contains the list of tenant's to which it is shared with.
The above data will be residing in the global database and when shared, the same can be referenced / replicated to the tenant's databases.

Approach 2

In this model, create an assignment and mark it as sharable. Any institution can then find from the list of shared data and clone / replicate the data for their use
There are some complexity in handling this case, like when an entity data is shared, it should somehow be taken to the common database or be shared with the remaining tenant's via a service bus like design, but there is complexity and security issues that are likely to be handled here.

Brainstorm the above approaches and share your questions if any for a specific model.

Share tenant data across schemas in multi-tenant PostgreSQL

1 Answers