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....????
xTremeBMS-multi-tennant.png
LVL 7
XGISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XGISAuthor Commented:
Not the response I was expecting but still gave me some ideas
0
XGISAuthor Commented:
Message to the moderator...., please can the image attached to the question be removed from the server???
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.