Re: When to create a new database
- Original Message - > From: "Ron Piggott" <ron.pigg...@actsministries.org> > Subject: Re: When to create a new database > > I would lean towards keeping it all together because of the speed > decrease between connecting to different databases. Heh, that consideration is a matter of semantics, and I'd guess you're used to Oracle? :-p What OP (presumably) meant was "in different schemas". Terminology is important, y'all. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new database
- Original Message - > From: "Reindl Harald" <h.rei...@thelounge.net> > Subject: Re: When to create a new database > > it makes zero sense since you can use different users for the same > database down to table and even column permissions No, it does make some sense in the case where part of the dataset is going to be accessed by multiple independent applications, and I think the generic sports bits may actually fit that. It's cleaner from a design point of view, and it prevents accidentally deleting that data when the original application is taken out of production. In my particular environment, we have quite a few of these generic databases; although from similar design ideology, they are also accessed only through their own REST interfaces, and not directly. /Johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 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
When to create a new database
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
Re: When to create a new database
I would lean towards keeping it all together because of the speed decrease between connecting to different databases. What I would tend to do is put some type of prefix that would keep the sets of tables together --- like lib_sports lib_rules lib_statistical lib_definitions data_players data_teams data_games Ron On 10/10/15 10:28, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new database
Am 10.10.2015 um 16:28 schrieb Richard Reina: 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? it makes zero sense since you can use different users for the same database down to table and even column permissions with default (crap) settings innodb anyways stores all in the same big file, and file_per_table is, well, per table signature.asc Description: OpenPGP digital signature
Re: When to create a new database
When I read the OP I was thinking: This is one for Reindl. And here we go. When dealing with data of this specific kind, you most definitely would want a date reference. A very small computer will be able to handle mane years of all kinds of weird sports statistics. You need to define the goal you are looking for, and then ask the question. On 2015-10-10 21:48, Reindl Harald wrote: Am 10.10.2015 um 16:28 schrieb Richard Reina: 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? it makes zero sense since you can use different users for the same database down to table and even column permissions with default (crap) settings innodb anyways stores all in the same big file, and file_per_table is, well, per table -- Mogens +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
