Web lists-archives.com

Re: When to create a new database

On 10/10/2015 10:28 AM, Richard Reina wrote:
If I were keeping tract of high school sports statistics and thus designed
the following tables:

sports, rules, statistical definitions


players, teams, games

Would it be a good or bad idea to put the first set of tables in a separate
database called "library" since they are used for reference and rarely
change? What would be the pros and cons of using two different databases?


The general rule is: are the tables all closely related (as in used for the same business purpose)? If they are, and possibly interdependent, then they normally belong in the same database.

However if some of of them are a derivatives of the others the it may make logical sense for the derivative tables to reside in their own database.

example: one database may be your "raw" data: every play, every statistic. The other database may be your "summary" data: the meta-statistics you get by combining or summarizing the raw data. Querying your already-summarized data will be much faster than trying to query your raw data for summaries every time you need them.

You may want to create the same set of tables in separate databases organized by sport. One DB for baseball, one for football, one for basketball, etc. That would make it easier for you to move just one shard of your entire data set to a new bigger server if the need arises. The problem with that design is that if you wanted to see a complete report for each player, then you have to query as many separate tables as you have sports (because each part of that player's history would be in a separate database).

If your MySQL instance is going to be acting as the back end to a web application, then you would probably want to split the tables into databases based on their function in your program: one database for your program's settings (users/accounts/access control, user options, user preferences,...) and a different database just for the statistical data.

A "database" is just a logically grouped set of tables. What is meant by "logic" in that previous sentence varies widely between each situation.

Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql