Best use of MySql database for multiple clients

Posted on 2010-09-01
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 250 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 250 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??
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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