Isolated and shared multi-tenancy

Hello Experts,

I am writing this question to get some advice..... I am in the process of creating an isolated and shared multi tenancy db solution (I hope I am using the correct terms) and the issue I am currently facing is the management of users and schema's.... what i have is a customers db, accounting db, assets db, projects db, ecommerce db and configuration db... where each db (except the config db) has its own web application that is hosted via a portal..... the problem, when a user goes to my site and purchases 3 months access to say 3 of the apps should I have there details stored in the ecommerce system and when payment is confirmed should I use a stored proceedure to create the schemas and record the companies schema and db access to the configuration db???? and if this is the case how should I manage the employees unter a company as they need to be able to be created by the designated sys admin....????
Who is Participating?
LowfatspreadConnect With a Mentor Commented:
how many physical databases are you actually going to have?
how interlinked are they?
  (will you have concurrency/,maintenance issues?)

are you actually contemplating establishing a new set of tables for each customer under a different schema?
how many customers do you realistically expect to support?
(wouldn't it be easier to add a customerid to most tables either as a foreign key or as part of a composite primary key
and restrict access to the data via that... ie most access is via a "logical join" to the User table)

I'm not clear what you are asking about the users...

surely you will just need to have a set of User / profile / role / function  tables which will allow the customer ...
to establish their employees as users, with access to a subset of the features they have purchased...
at a minimum you need to allow for full access and just enquiry access... the complications come in chosing logical sets of features/functions but only you know what your business is attempting to support.
XGISAuthor Commented:
Not the response I was expecting but still gave me some ideas
XGISAuthor Commented:
Message to the moderator...., please can the image attached to the question be removed from the server???
All Courses

From novice to tech pro — start learning today.