Best use of MySql database for multiple clients


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.

Who is Participating?
GMGeniusConnect With a Mentor Commented:
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
learningtechnologiesConnect With a Mentor Commented:
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.
LmillardAuthor Commented:
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??
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.
The other option here then would be a seperate DB just for the CC details?
That's an excellent thought, GMGenius -
LmillardAuthor Commented:
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.
All Courses

From novice to tech pro — start learning today.