Database performance (like system performance) is always driven by a
give and take process, a trade-off between resources (this includes
design expertise) available, and the required results.
How many records are you dealing with?
How often are records are being added and/or updated?
How many users will be running queries simultaneously, number of
readonly, and number of updates and inserts?
If only a handful of users, and less than 100,000 records, that rarely
change, then you'll probably never have performance problems.
However, it you have 100,000 users running queries simultaneously, and
changing records regularly, then you may have some issues to address.
Their are literally hundreds of solutions to performance problems, and
the right solutions will depend on your particular requirements.
Security, is much the same, dependent on the security requirements.
Example:
Security auditors claimed that the marketing product database was not
secure.
They said that it needed to be more secure.
The CEO of the company asked a simple question, he said:
"Whats the worst that could happen if an outsider gains access to this
database?"
Answer: "They might buy one of your products!"
Lesson learned: Don't waste your time securing data that doesn't need to
be secured.
So, what are your requirements?
On 12/23/2012 09:11 PM, Chad Vernon wrote:
Hi there,
I'm designing an animation studio database to track assets across multiple
shows. My original design was to have a "show" table to track which asset
belongs to which show with a show_id column. A couple coworkers suggested
having a database per show because they are worried about performance and
reliability vs a single database as the number of shows grows. I don't
think reliability would be different because it's all on the same MySQL
server anyways. Is that assumption correct? I've read a few articles
about multi-tenant database design, however the motivation for multiple
databases seems to be security which doesn't apply to us. Am I correct in
trying to push a single database design for this? If it helps, we have no
dedicated dba and currently a single mysql server machine.
Thanks,
Chad
