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
and
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?
Thanks
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
