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??
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.


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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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…
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…

622 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