Separate Schemas by Industry or by Application functionality

application-designdatabase-designschema

I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web based.

I might use Postgresql, I would go for a Single DB and shared schema. (The data volume is low and the mo. of concurrent users will be 400 max.).

The retail app addresses billing, inventory etc.,(backoffice/mini-ERP). The education app covers student enrollment, management and student prospecting etc.,(mostly Frontoffice – Student management, Knowledge Management, Leads, some Marketing etc.,)

The initial thought was to have a common public Schema (with addresses, contacts and other common table objects) and have specific Schemas for Retail and Education. i.e., Industry vertical -wise. As I add more customers in different businesses than existing I will end up adding additional schema. This could be one approach.

Or I was thinking should I go for a public schema and separate schema for ERP type functionality, CRM – like functionality etc., This way the schema will be Horizontal i.e., based on functionality rather than Industry. Tomorrow I can add a Chemical Retail or Medical contact management client or a Restaurant client who need billing and fit them easily into existing data models and can handle the variations in application code rather than creating new schemas for each business line.

Confused on which approach will be better in the long run?

EDIT: Or should I just not bother and stick with the public schema and handle variations in code? or are there any compelling reasons for the above approaches or any other better approaches than this?

Best Answer

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.

I am assuming that:

  • you will have separate application for each industry,
  • they have not very much in common (they really cover different business aspects)

I think that one of acceptable solutions in this case would be:

  • a separate database for each industry
  • in every database, single shared public schema for shared data, including common dictionaries, all non-customer-dependent data.
  • one separate database user account + private schema for each customer. You might use postgres feature of table inheritance to conform to some public table.

Issues that Simon puts in his comment are really important. So you will have to enforce a very strict policy regarding GRANTs in your database.

In PostgreSQL, it is possible to achieve (as well in many other RDBMS). The key to achieving multi-tenant solution would be intelligent usage of schemas, roles, search_path setting. See http://www.postgresql.org/docs/current/static/ddl-schemas.html. Actually what I propose will in a way emulate what Oracle does. If you need help on details please ask.

Related Question