2

i need some information about multiple databases for a multi-tenant application ?

I am working on a project in which we are maintaining separate database for every company?

The Companies Database contain customer Data.

There is a common Database to store common information across these databases. Example Customer ID,Customer Contact Number and Customer Email etc.

A New Database is created for every new company.

If one customer belongs to company1 and same customer is added in company 2 then we need to give same customer id from company 1 to that customer across all companies.

There are many such scenarios similar to the above example.

Can any one tell me how to manage multiple databases(companies) and same customers across all companies.

Improve this question
0

2 Answers 2

Reset to default
1

You should store all information that is common for all companies in one database and copy the data that are needed to the database that needs the information. Adding a new customer would be done in the common db. After that the information will be copied to company1. If it's needed in company2, then the data will be copied to company2. Make sure you also add an update process to keep the copied information in sync. Alternatively, you could keep the customer information in the main db and link to it. However, this would only be recommended if all db's are on the same SQL Server instance.

Improve this answer
3
  • Thank you @Lmu92 for your useful information.Right now i am doing same way.But i am giving update for customer information in specific company database.It is not to be updated in common database , Because information security issues.Example for company 1 customer gave name as test but in company 2 he gave name as testing.
    – user2633530
    Aug 23, 2014 at 11:26
  • If in one database the name is test and in the other one it's testing, how do you know both refer to the same? However, the update should be against the main table being effectiv for all customers. If this can't be done, I'd recommend not to use a concept relying on identical customer id values. Instead use completely stand-alone versions.
    – Lmu92
    Aug 23, 2014 at 13:02
  • Thank you @Lmu92, We are identifying customer based on the contact number.
    – user2633530
    Aug 25, 2014 at 4:36
1

See my answer to this question which is similar in scope to your own question, except that your project is already up and running, so you can ignore the recommendation to use PostgreSQL instead of MySQL if starting from scratch.

As Lmu92 points out, the one thing that is critical in a scenario like this is to have a very clear set of procedures about inserting, updating (and deleting?) your common data. If you make a mistake with one customer's data, that's bad enough, but only one customer is affected, make a mistake with the common data and you'll have all of them on the phone!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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