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?