Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Best use of MySql database for multiple clients

Posted on 2010-09-01
Medium Priority
Last Modified: 2012-05-10

I have an eCommerce and CRM application that will be provided to multiple clients but always hosted on my server. At the moment I am looking to replicate a virgin state database for each new client but am wondering if it would be better to load all orders, accounts and other data into a single large database and then ensure that all client specific data is stamped to ensure accurate selects and updates.

I know that this is dependent on number of records etc but this is an unknown at the moment and I am looking for method advice more than anything. My concern is that with multiple databases these would become increasingly harder to administrate whereas the main issue with the single larger database would be optimisation.

Any thoughts and advice would be gratefully received.

Question by:Lmillard
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 12

Accepted Solution

GMGenius earned 1000 total points
ID: 33575720
I would suggest 1 database.
You would have a table for clients with a unique ID for each one then for orders etc you should look to only store the client record ID on each row
Then selecting across tables with joins to get the client information relating to any given order/other data

Assisted Solution

learningtechnologies earned 1000 total points
ID: 33576385
Will these customer records contain any financially sensitive data, such as credit card numbers?
If so, then each client will need to have their own database separated from each other.
You will also need to be careful extracting the data so that no hashed or plain text card numbers are ever stored out in a file on the file system.
Of course there's a lot more to CISP compliance - but those are the large issues.

Author Comment

ID: 33577955
I am certainly leaning towards the single database method so It may be worth me not storing the credit card details then as I am reluctant to go with an uncontrollable data model just because Visa say I have to. Thanks very much for pointing this out as it certainly is important. Surely there must be a way around this as I can't imagine the larger hosted applications companies are prepared to generate thousands of databases for CISP??
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Expert Comment

ID: 33578837
From what I've seen, each party responsible for the transactions of one business entity maintains a separate database for each business entity.  Not a database server, but a database is created for each business entity.
I don't know if your particular situation would require this, but that's what I have seen.
Your clearinghouse provider may provide this separation for you . . . I don't know without all of your details of your particular situation -
A single database would be the most manageable.  I simply don't know if it is the most secure from a CISP standpoint.
Just some thoughts for you . . .
I hope this helps.
LVL 12

Expert Comment

ID: 33578844
The other option here then would be a seperate DB just for the CC details?

Expert Comment

ID: 33579797
That's an excellent thought, GMGenius -

Author Comment

ID: 33583972
Sounds like a good solution.
I am now sure that the single database option is the best way forward combined with a particular effort to ensure safe storage and retrieval of financial data.

Thank you both for your advice and time with this one, it us much appreciated.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question