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?
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. :D
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);