Solved

Best use of MySql database for multiple clients

Posted on 2010-09-01
7
311 Views
Last Modified: 2012-05-10
Hi,

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.

Thanks
0
Comment
Question by:Lmillard
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
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
0
 
LVL 2

Assisted Solution

by:learningtechnologies
learningtechnologies earned 250 total points
ID: 33576385
Will these customer records contain any financially sensitive data, such as credit card numbers?
http://usa.visa.com/merchants/risk_management/cisp_tools_faq.html
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.
0
 

Author Comment

by:Lmillard
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??
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:learningtechnologies
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.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33578844
The other option here then would be a seperate DB just for the CC details?
0
 
LVL 2

Expert Comment

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

Author Comment

by:Lmillard
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now