3

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?

Improve this question
5
  • Personally I would be very very wary of A: mixing different customers' data in one database (unless you have very very strict isolation that's a privacy lawsuit waiting to happen), and B: trying to make one schema to rule them all. Something about that entire idea gives me the heebie jeebies.
    – Simon Righarts
    Commented Jan 8, 2012 at 9:54
  • @Simon: I agree it is a valid concern. Based on what I read, in the SaaS land, many biggies including Salesforce.com use this "multi-tenant data being in single DB" approach (I might be wrong). With proper security measures, can't the data be co-located in a shared database? I am referring to this link. [MSDN article]: msdn.microsoft.com/en-us/library/aa479086.aspx
    – tmbsundar
    Commented Jan 8, 2012 at 12:39
  • This is one of those things that will get you a different answer depending on who you ask. On dba.se the multitennanted approach makes people uncomfortable because it introduces data control complexities. If you asked on SO you'd get people uncomfortable with multiple schemas because is isn't DRY in the code. Either way you go you have some extra work to do so just pick an approach and go in with your eyes open.
    – Joel Brown
    Commented Jan 8, 2012 at 12:59
  • Joel, but multiple schemas does not necessarily contrast with DRY in the code. at least when you use PostgreSQL.
    – filiprem
    Commented Jan 8, 2012 at 14:56
  • filiprem - I didn't say that it is or isn't DRY with PostgreSQL. I just pointed out that different approaches have different pros and cons and that DBAs and devs are going to have different sensitivities. No matter what approach OP takes, he needs to make careful choices. As you yourself pointed out in your answer.
    – Joel Brown
    Commented Jan 9, 2012 at 2:02

1 Answer 1

Reset to default
4

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.

Improve this answer
6
  • Thanks for the answer. I am pretty new to the SaaS multi-tenant architecture. Based on what I read and understood, e,g.,(), I was thinking of taking the Shared schema, Shared Database approach.
    – tmbsundar
    Commented Jan 8, 2012 at 12:09
  • Thanks for the answer. I am pretty new to the SaaS multi-tenant arch.Based on what I read MSDN article, I liked the "Shared schema, Shared Database" (SSSD) approach with tenant-ids and view filters to isolate data access. It might be a separate DB as you suggested per app. But, do I really need a schema per customer? Can't I manage with a single schema per app. (discounting public)? I was thinking that Salesforce.com is running with this approach.(I might be wrong). What are the downsides of taking this approach? Or is it doable?
    – tmbsundar
    Commented Jan 8, 2012 at 12:34
  • @tmbsundar, thanks for your interesting discussion. What you describe is surely doable. What I propose is customer == tenant == schema. If you follow this model in postgres, it is easy to separate customers, enforce proper data security, so you will get relatively easy solution for issues that Simon suggested. And it does not violate DRY rule.
    – filiprem
    Commented Jan 8, 2012 at 14:59
  • .@filiprem,Thanks for taking time to answer this. The searchpath definitely gets the schema qualifier out of the way, making easy to write sqls. But, as the schema are used as namespcaes in pgsql, for each schema I guess one set of table, db objects will be created. If I end up say having 'x' number of schema, will this take a) more space b) any performance impact? And also, are there any economic impact like increased cost due to more RAM or disk space etc., Thanks again.
    – tmbsundar
    Commented Jan 8, 2012 at 16:57
  • @tmbsundar, yes, one set of tables for each schema, naturally it will occupy system catalog and take disk space and RAM when active. For a test, I've just cloned my sample schema (~ 50 empty tables + many constraints, FK, CHECKs etc) 100 times and the database grows to about 200MB. INHERITS option might be useful to reduce this overhead and enable easier upgrades (one template schema and customer schemas subclassed from it).
    – filiprem
    Commented Jan 9, 2012 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.