Postgresql – Will table inheritance in PostgreSql suit this situation

database-designpostgresqlschema

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.

So my main aim is to have a Main schema in this DB, which I am going to use to control certain services with, and have multiple schema's linked to this, which is going to be the different clients data.

I have read up on table inheritance, but have never came across anyone using inheritance with an effect similar to that of DB Migrations.
What I want to achieve is basically have a default schema, with the clients schema's all inheriting from the default. This to me gives control of creating/dropping/altering objects to all schema's by just altering a single schema.

Could this be viable? Has anyone else had any experience doing something similar? Does anyone have any suggestion?

UPDATE

This is what I've done. However, I was under the impression that, if you look at the below code, schema 'client1' will be all I need to do, however I was wrong. 😀

So anyway, below is the script I have done, which should take care of everything I need for now – I am going to use the script for the 'client2' schema. Good or Bad?

-- public schema --
CREATE TABLE public.Users (
   Id bigserial primary key,
   Login text unique,
   check (false) NO INHERIT 
);

CREATE TABLE public.Roles (
   Id bigserial primary key,
   RoleName text unique,
   check (false) NO INHERIT 
);

CREATE TABLE public.UserRoles (
   Id bigserial primary key,
   UserId bigint references users(id),
   RoleId bigint references roles(id),
   unique (UserId, RoleId),
   check (false) NO INHERIT 
);


-- client1 schema --
CREATE TABLE client1.Users (
)
INHERITS(public.Users);

CREATE TABLE client1.Roles (
)
INHERITS(public.Roles);

CREATE TABLE client1.UserRoles (
)
INHERITS(public.UserRoles);


-- client2 schema --
CREATE TABLE client2.Users (
  primary key (id),
  Unique (Login)
)
INHERITS(public.Users);

CREATE TABLE client2.Roles (
  primary key (id),
  Unique (RoleName)
)
INHERITS(public.Roles);

CREATE TABLE client2.UserRoles (
  primary key (id),
  foreign key (UserId) REFERENCES client2.Users(id),
  foreign key (RoleId) REFERENCES client2.Roles(id),
  unique (UserId, RoleId)
)
INHERITS(public.UserRoles);

Best Answer

It is viable provided that you note that keys and indexes must be declared on each child table.

In fact this is pretty close to an ideal use for table inheritance. Just make sure you are familiar with the gotchas, and that you pay close attention to foreign key creation in the user schemas.

One caveat is that you can't cascade CREATE statements into all the schemas. This would have to be scripted. You could however create plpgsql functions to do this. Just note that such statements usually are not parameterized so you need to use quote_ident() and quote_literal as appropriate.

As per request here's an example. In the master schema. Note the key and unique constraints here are for documentation purposes only.

CREATE SCHEMA master;
SET SEARCH_PATH="master;

CREATE TABLE users (
   id bigserial not null unique,
   login text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE roles (
   id bigserial not null unique,
   role_name text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE user_roles (
   user_id bigint references users(id),
   role_id bigint references roles(id),
   primary key (user_id, role_id)
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

RESET SEARCH_PATH;

Naturally if not on PostgreSQL 9.2 drop the check constraints since they would be inherited in prior versions and then no tables could have rows.

Then in a child schema:

CREATE SCHEMA customer123;
SET SEARCH_PATH 'customer123';

CREATE TABLE users(unique (id), primary key(login)) INHERITS (master.users);
CREATE TABLE roles(unique (id), prikary key(role_name)) INHERITS (master.roles);
CREATE TABLE user_roles(
     foreign key users_id REFERENCES customer123.users(id),
     foreign key role_id REFERENCES customer123.roles(id),
     primary key (users_id, role_id)
);
RESET SEARCH_PATH;

Note in this case you must carefully control access to the master schema because:

SELECT tableoid, login from master.users;

gives you every user account of every tenant plus enough info to determine which tenant it is.

Related Question