Are there any guidelines/best practices for deciding what type of data should be stored in the database?
是否有任何指导/最佳实践来决定应在数据库中存储哪种类型的数据?
For ex., is it ok to use database to store
例如,是否可以使用数据库进行存储
- Application logs
- Configuration details (like server IP addresses etc.)
- System information (e.g., names of shell scripts, scheduling information for batch jobs, batch jobs status etc.)
配置详细信息(如服务器IP地址等)
系统信息(例如,shell脚本的名称,批处理作业的调度信息,批处理作业状态等)
I have seen applications that use database for storing these. Is this acceptable? What are the pros and cons of such a design?
我见过使用数据库存储这些应用程序的应用程序。这可以接受吗?这种设计的优点和缺点是什么?
8 个解决方案
#1
6
To answer this question we have to understand what database storage provides that isn't available in say, flat file storage.
要回答这个问题,我们必须了解数据库存储提供的平面文件存储不可用。
- security - You can store stuff and be sure that updates, deletes and views would be controlled
- audit - you can keep track of who made changes and when
- distributed servers - if you have multiple application servers accessing a single database, you avoid storing the same data in multiple places
安全性 - 您可以存储内容并确保可以控制更新,删除和视图
审计 - 您可以跟踪谁进行了更改以及何时进行更改
分布式服务器 - 如果您有多个应用程序服务器访问单个数据库,则可以避免在多个位置存储相同的数据
If these are desirables for your data, it's a good idea to store them in the database.
如果这些是您的数据的desirables,最好将它们存储在数据库中。
#2
4
Application logs
Although it often is a good idea to limit the data in the database to a specific time range (e.g. dump/archive/condense to stats everything that's older than 3 months), having the logs in database allows very fast and easy analysis of the data. Need to see what a specific user has done? "SELECT * FROM logs WHERE User = 'bla'". Need to find out why the system crashed at a specific time? "SELECT * FROM logs WHERE Timestamp BETWEEN failure - 1 hour AND failure + 5 minutes".
虽然将数据库中的数据限制在特定的时间范围内(例如,转储/归档/压缩到超过3个月的所有内容)通常是一个好主意,但将数据存储在数据库中可以非常快速,轻松地分析数据。需要查看特定用户的操作? “SELECT * FROM logs WHERE User ='bla'”。需要找出系统在特定时间崩溃的原因? “SELECT * FROM logs WHERE Timestamp BETWEEN failure - 1小时AND failure + 5 minutes”。
Configuration details (like server IP addresses etc.)
配置详细信息(如服务器IP地址等)
That depends on the configuration details. Some yes, some no. Everything that's valid for applications that run on more than one client (e.g. websites) and that is probably changing quite often (i.e. user settings) should go in the database. For more or less static options, I prefer to use a config file.
这取决于配置细节。有些是,有些没有。对于在多个客户端(例如网站)上运行且可能经常更改的应用程序(即用户设置)有效的所有内容都应该放在数据库中。对于或多或少的静态选项,我更喜欢使用配置文件。
System information (e.g., names of shell scripts, scheduling information for batch jobs, batch jobs status etc.)
系统信息(例如,shell脚本的名称,批处理作业的调度信息,批处理作业状态等)
I guess that's almost the same as config details. If it changes: database. If it's static: config file. Shell scripts will usually be static. Scheduling information and status will change over time.
我猜这与配置细节几乎相同。如果它改变:数据库。如果它是静态的:配置文件。 Shell脚本通常是静态的。计划信息和状态将随时间而变化。
#3
3
We have stored everything in the database on the last few projects and it really helps when moving from development to production as there is very little to configure in the application itself.
我们已经在最后几个项目中存储了数据库中的所有内容,这在从开发到生产的过程中确实很有用,因为在应用程序本身中配置很少。
Logging to the database can be useful (Log4j for e.g.) as it allows widespread access to the logs for the testers and analysts.
记录到数据库可能很有用(例如Log4j),因为它允许广泛访问测试人员和分析人员的日志。
I guess it depends on your situation. Everything that is stored in the database adds a level of cemplexity to the system. It is easier to read a file than to access a database to get the same information from code. A probable rule if thumb would be to say that the larger the system, more of it should be stored in the database.
我想这取决于你的情况。存储在数据库中的所有内容都会为系统增加一定程度的复杂性。读取文件比访问数据库以从代码获取相同信息更容易。一个可能的规则,如果拇指是说系统越大,它应该存储在数据库中。
#4
2
A small point: 99% of the time it's a terrible idea to store configuration in the DB. Config is too important to lose to a DB connection gone south: it needs to be 100% bullet proof.
一个小问题:99%的时间将配置存储在数据库中是个糟糕的主意。配置太重要了,不能丢失到南方的数据库连接:它需要100%防弹。
#5
2
RE: Config data It might be a good idea to keep config data in the database to make it easier to edit it and keep track of the changes but then wright it out to a config file for the actual program to read.
RE:配置数据最好将配置数据保存在数据库中,以便更容易编辑它并跟踪更改,然后将其调到配置文件中以供实际程序读取。
-
Why should apache have to know anything about your database information to be able to get to its configuration?
为什么apache必须知道有关您的数据库信息的任何信息才能进入其配置?
-
Why should your FTP server stop working when the database is down?
数据库关闭时,为什么FTP服务器会停止工作?
RE: Application logs
RE:应用程序日志
As stated earlier, a database can make log analyzing a lot easier, but I urge you to consider the log-to-file-and-batch-import-later pattern.
如前所述,数据库可以使日志分析变得更加容易,但我建议您考虑日志到文件和批量导入的模式。
Performance issues
Databases are great for getting random bits of data out and putting random bits of data in. Log data mostly is not written randomly but in a continues stream of data that is perfect for putting in a file one line after an other. You can't beat the performance of a flat file when it comes to writing the data. There's not a lot of things that can break with a flat file either. This also lets the database concentrate on doing the actual business work.
数据库非常适合将随机数据输出并输入随机数据。日志数据大多不是随机写入的,而是连续写入的数据流,非常适合一行接一行放入文件。在编写数据时,您无法击败平面文件的性能。也没有很多东西可以打破平面文件。这也使数据库可以专注于实际的业务工作。
Then later on you can collect all the logged data from the file, parse it, do any required post processing (like looking up host names from IP addresses) and put it into a database table. You do this as often as you find necessary. For my website I really don't need to be able to view the visitor stats change from one minute to the other so I run the log batch at night. If you need up to date info you can just as well run the batch import every 60 seconds, but this will still be better than doing one extra INSERT statement for every actual business transaction (depending on how much you log, of course).
然后,您可以从文件中收集所有记录的数据,解析它,执行任何所需的后处理(如从IP地址查找主机名)并将其放入数据库表中。您可以在必要时经常这样做。对于我的网站,我真的不需要能够查看访问者统计信息从一分钟到另一分钟的变化,所以我在晚上运行日志批处理。如果您需要最新信息,您也可以每60秒运行批量导入,但这仍然比为每个实际业务事务执行一个额外的INSERT语句更好(当然,这取决于您记录的数量)。
Security
-
How do you log a failed database connection if the database is your log engine?
如果数据库是您的日志引擎,如何记录失败的数据库连接?
-
How do you investigate why a system crashed if the database went down early during the events involved in the crash?
如何在崩溃涉及的事件期间数据库早期崩溃,您如何调查系统崩溃的原因?
So I think you should consider when you need the log data in the database and why you need it in there.
所以我认为您应该考虑何时需要数据库中的日志数据以及为什么需要它。
#6
1
One thing that hasn't been mentioned yet is if you shove things like app configuration in the database, you can't put it under version control as easy.
尚未提及的一件事是,如果您在数据库中推送应用程序配置等内容,则无法轻松将其置于版本控制之下。
For example, some CMS's like to shove HTML templates into the database instead of as files. I personally think this is poor design. You can't version any of the changes you make to the templates and worse, all you ever do is copy & paste from a real text editor into the wimpy text editor in the browser.
例如,某些CMS喜欢将HTML模板推送到数据库而不是文件中。我个人认为这是糟糕的设计。您无法对模板所做的任何更改进行版本更糟糕,更糟糕的是,您所做的只是将实际文本编辑器复制并粘贴到浏览器中的wimpy文本编辑器中。
Bottom line? Ask yourself if this is something you want versioned. If yes, keep it out of the database. If no, sure, put it in the database.
底线?问问自己这是否是你想要版本化的东西。如果是,请将其保留在数据库之外。如果不是,请确保将其放入数据库中。
#7
0
Focus on ease of use and maintenance. The only logs I store in a database are put there by triggers that error out because that's easiest. But for everything else, searching and parsing text logs is faster and easier. If your app crashes, looking at a text config file is easier than looking in the db, especially for new maintainers. It's much, much easier for a new person to come along and see an app.properties file in the config/ directory than to know to look in a table in the database.
注重易用性和维护。我存储在数据库中的唯一日志由错误输出的触发器放在那里,因为这是最简单的。但对于其他一切,搜索和解析文本日志更快更容易。如果您的应用程序崩溃,查看文本配置文件比查看数据库更容易,尤其是对于新的维护者。对于新人来说,在config /目录中查看app.properties文件要比查看数据库中的表更容易。
In addition, you can more easily store config files in source control if they're text files than if they're in the database. And this is massively important, believe me. You do not want to debug an app where you've lost the config file settings that caused the error. If you have a database crash or corruption, you could lose the logs and config settings which might make finding the problem impossible.
此外,如果配置文件是文本文件,则可以更轻松地将配置文件存储在源代码管理中,而不是存储在数据库中。相信我,这非常重要。您不想调试丢失导致错误的配置文件设置的应用程序。如果您遇到数据库崩溃或损坏,您可能会丢失日志和配置设置,这可能导致无法找到问题。
#8
0
If you're developing a small, static website, then I would agree with most of the points already made. However, if you have a website that allows for users to add content via the production site I would argue that putting configuration in the database complicates the deployment pipeline to the point that keeping it out of the database is preferable.
如果您正在开发一个小型的静态网站,那么我同意已经提出的大多数观点。但是,如果您有一个允许用户通过生产站点添加内容的网站,我认为将配置放入数据库会使部署管道变得复杂,以至于将其保留在数据库之外是可取的。
If you're trying to push an update from dev to production, clients are pushing content to production, and your config and content are both in the same database then you need to target only tables with configuration data to be overwritten. This 'can' be a trivial amount of extra work on your part, but it depends on the scale of the application and whether or not you're making use of someone elses code. Consider drupal sites. If users are adding content then for deployment you need to target specific database tables to be overwritten. Since drupal has several tables (none of which have config in their names) you'll need to do some research to figure out what can be overwritten and what can't. Now, what happens if something changes in drupal's database layout? Deployment pipeline could break and it's more extra work for you. What happens when you add a new plugin? More config tables, so changes in your deployment scripts are needed. More work for you. Should you eventually move on from this project, you would be expected to leave information with the new developer to explain what you've done with regards to these deployment issues. More work for you.
如果您正在尝试将更新从开发推送到生产,客户端正在将内容推送到生产环境,并且您的配置和内容都在同一个数据库中,那么您只需要定位具有要覆盖的配置数据的表。这个“可以”对你来说是一项微不足道的额外工作,但这取决于应用程序的规模以及你是否正在使用别人的代码。考虑drupal网站。如果用户正在添加内容然后进行部署,则需要将特定数据库表作为目标进行覆盖。由于drupal有几个表(没有一个表的名称配置),你需要做一些研究来弄清楚什么可以被覆盖,什么不可以。现在,如果drupal的数据库布局发生了变化,会发生什么?部署管道可能会中断,这对您来说是更多的额外工作。添加新插件会发生什么?更多配置表,因此需要更改部署脚本。为你做更多的工作。如果您最终离开此项目,您将需要与新开发人员分享信息,以解释您对这些部署问题所做的工作。为你做更多的工作。
Consider what would happen if the config wasn't in the database but in your applications directory structure instead. Save the config changes to git/svn/etc, push the changes to your server box and overwrite older files. DONE. The database will be touched less when you roll out changes, your config can be put under version control, and your application is now directly coupled with the configuration it makes use of (which makes sense). This is more valuable for moderate/large scale applications or applications which make use of pre-built components/frameworks that you have no control over, then for small scale applications. However, it works at all scales were as storing config in databases becomes more troublesome as your applications grow and your deployment pipeline becomes complicated.
考虑如果配置不在数据库中但在应用程序目录结构中会发生什么。将配置更改保存到git / svn / etc,将更改推送到服务器框并覆盖旧文件。 DONE。当您推出更改时,数据库将被触及较少,您的配置可以置于版本控制之下,并且您的应用程序现在直接与其使用的配置相关联(这是有道理的)。对于中等/大规模应用程序或使用您无法控制的预构建组件/框架的应用程序而言,这对于小规模应用程序更有价值。但是,它适用于所有规模,因为随着应用程序的增长和部署管道变得复杂,数据库中的存储配置变得更加麻烦。
#1
6
To answer this question we have to understand what database storage provides that isn't available in say, flat file storage.
要回答这个问题,我们必须了解数据库存储提供的平面文件存储不可用。
- security - You can store stuff and be sure that updates, deletes and views would be controlled
- audit - you can keep track of who made changes and when
- distributed servers - if you have multiple application servers accessing a single database, you avoid storing the same data in multiple places
安全性 - 您可以存储内容并确保可以控制更新,删除和视图
审计 - 您可以跟踪谁进行了更改以及何时进行更改
分布式服务器 - 如果您有多个应用程序服务器访问单个数据库,则可以避免在多个位置存储相同的数据
If these are desirables for your data, it's a good idea to store them in the database.
如果这些是您的数据的desirables,最好将它们存储在数据库中。
#2
4
Application logs
Although it often is a good idea to limit the data in the database to a specific time range (e.g. dump/archive/condense to stats everything that's older than 3 months), having the logs in database allows very fast and easy analysis of the data. Need to see what a specific user has done? "SELECT * FROM logs WHERE User = 'bla'". Need to find out why the system crashed at a specific time? "SELECT * FROM logs WHERE Timestamp BETWEEN failure - 1 hour AND failure + 5 minutes".
虽然将数据库中的数据限制在特定的时间范围内(例如,转储/归档/压缩到超过3个月的所有内容)通常是一个好主意,但将数据存储在数据库中可以非常快速,轻松地分析数据。需要查看特定用户的操作? “SELECT * FROM logs WHERE User ='bla'”。需要找出系统在特定时间崩溃的原因? “SELECT * FROM logs WHERE Timestamp BETWEEN failure - 1小时AND failure + 5 minutes”。
Configuration details (like server IP addresses etc.)
配置详细信息(如服务器IP地址等)
That depends on the configuration details. Some yes, some no. Everything that's valid for applications that run on more than one client (e.g. websites) and that is probably changing quite often (i.e. user settings) should go in the database. For more or less static options, I prefer to use a config file.
这取决于配置细节。有些是,有些没有。对于在多个客户端(例如网站)上运行且可能经常更改的应用程序(即用户设置)有效的所有内容都应该放在数据库中。对于或多或少的静态选项,我更喜欢使用配置文件。
System information (e.g., names of shell scripts, scheduling information for batch jobs, batch jobs status etc.)
系统信息(例如,shell脚本的名称,批处理作业的调度信息,批处理作业状态等)
I guess that's almost the same as config details. If it changes: database. If it's static: config file. Shell scripts will usually be static. Scheduling information and status will change over time.
我猜这与配置细节几乎相同。如果它改变:数据库。如果它是静态的:配置文件。 Shell脚本通常是静态的。计划信息和状态将随时间而变化。
#3
3
We have stored everything in the database on the last few projects and it really helps when moving from development to production as there is very little to configure in the application itself.
我们已经在最后几个项目中存储了数据库中的所有内容,这在从开发到生产的过程中确实很有用,因为在应用程序本身中配置很少。
Logging to the database can be useful (Log4j for e.g.) as it allows widespread access to the logs for the testers and analysts.
记录到数据库可能很有用(例如Log4j),因为它允许广泛访问测试人员和分析人员的日志。
I guess it depends on your situation. Everything that is stored in the database adds a level of cemplexity to the system. It is easier to read a file than to access a database to get the same information from code. A probable rule if thumb would be to say that the larger the system, more of it should be stored in the database.
我想这取决于你的情况。存储在数据库中的所有内容都会为系统增加一定程度的复杂性。读取文件比访问数据库以从代码获取相同信息更容易。一个可能的规则,如果拇指是说系统越大,它应该存储在数据库中。
#4
2
A small point: 99% of the time it's a terrible idea to store configuration in the DB. Config is too important to lose to a DB connection gone south: it needs to be 100% bullet proof.
一个小问题:99%的时间将配置存储在数据库中是个糟糕的主意。配置太重要了,不能丢失到南方的数据库连接:它需要100%防弹。
#5
2
RE: Config data It might be a good idea to keep config data in the database to make it easier to edit it and keep track of the changes but then wright it out to a config file for the actual program to read.
RE:配置数据最好将配置数据保存在数据库中,以便更容易编辑它并跟踪更改,然后将其调到配置文件中以供实际程序读取。
-
Why should apache have to know anything about your database information to be able to get to its configuration?
为什么apache必须知道有关您的数据库信息的任何信息才能进入其配置?
-
Why should your FTP server stop working when the database is down?
数据库关闭时,为什么FTP服务器会停止工作?
RE: Application logs
RE:应用程序日志
As stated earlier, a database can make log analyzing a lot easier, but I urge you to consider the log-to-file-and-batch-import-later pattern.
如前所述,数据库可以使日志分析变得更加容易,但我建议您考虑日志到文件和批量导入的模式。
Performance issues
Databases are great for getting random bits of data out and putting random bits of data in. Log data mostly is not written randomly but in a continues stream of data that is perfect for putting in a file one line after an other. You can't beat the performance of a flat file when it comes to writing the data. There's not a lot of things that can break with a flat file either. This also lets the database concentrate on doing the actual business work.
数据库非常适合将随机数据输出并输入随机数据。日志数据大多不是随机写入的,而是连续写入的数据流,非常适合一行接一行放入文件。在编写数据时,您无法击败平面文件的性能。也没有很多东西可以打破平面文件。这也使数据库可以专注于实际的业务工作。
Then later on you can collect all the logged data from the file, parse it, do any required post processing (like looking up host names from IP addresses) and put it into a database table. You do this as often as you find necessary. For my website I really don't need to be able to view the visitor stats change from one minute to the other so I run the log batch at night. If you need up to date info you can just as well run the batch import every 60 seconds, but this will still be better than doing one extra INSERT statement for every actual business transaction (depending on how much you log, of course).
然后,您可以从文件中收集所有记录的数据,解析它,执行任何所需的后处理(如从IP地址查找主机名)并将其放入数据库表中。您可以在必要时经常这样做。对于我的网站,我真的不需要能够查看访问者统计信息从一分钟到另一分钟的变化,所以我在晚上运行日志批处理。如果您需要最新信息,您也可以每60秒运行批量导入,但这仍然比为每个实际业务事务执行一个额外的INSERT语句更好(当然,这取决于您记录的数量)。
Security
-
How do you log a failed database connection if the database is your log engine?
如果数据库是您的日志引擎,如何记录失败的数据库连接?
-
How do you investigate why a system crashed if the database went down early during the events involved in the crash?
如何在崩溃涉及的事件期间数据库早期崩溃,您如何调查系统崩溃的原因?
So I think you should consider when you need the log data in the database and why you need it in there.
所以我认为您应该考虑何时需要数据库中的日志数据以及为什么需要它。
#6
1
One thing that hasn't been mentioned yet is if you shove things like app configuration in the database, you can't put it under version control as easy.
尚未提及的一件事是,如果您在数据库中推送应用程序配置等内容,则无法轻松将其置于版本控制之下。
For example, some CMS's like to shove HTML templates into the database instead of as files. I personally think this is poor design. You can't version any of the changes you make to the templates and worse, all you ever do is copy & paste from a real text editor into the wimpy text editor in the browser.
例如,某些CMS喜欢将HTML模板推送到数据库而不是文件中。我个人认为这是糟糕的设计。您无法对模板所做的任何更改进行版本更糟糕,更糟糕的是,您所做的只是将实际文本编辑器复制并粘贴到浏览器中的wimpy文本编辑器中。
Bottom line? Ask yourself if this is something you want versioned. If yes, keep it out of the database. If no, sure, put it in the database.
底线?问问自己这是否是你想要版本化的东西。如果是,请将其保留在数据库之外。如果不是,请确保将其放入数据库中。
#7
0
Focus on ease of use and maintenance. The only logs I store in a database are put there by triggers that error out because that's easiest. But for everything else, searching and parsing text logs is faster and easier. If your app crashes, looking at a text config file is easier than looking in the db, especially for new maintainers. It's much, much easier for a new person to come along and see an app.properties file in the config/ directory than to know to look in a table in the database.
注重易用性和维护。我存储在数据库中的唯一日志由错误输出的触发器放在那里,因为这是最简单的。但对于其他一切,搜索和解析文本日志更快更容易。如果您的应用程序崩溃,查看文本配置文件比查看数据库更容易,尤其是对于新的维护者。对于新人来说,在config /目录中查看app.properties文件要比查看数据库中的表更容易。
In addition, you can more easily store config files in source control if they're text files than if they're in the database. And this is massively important, believe me. You do not want to debug an app where you've lost the config file settings that caused the error. If you have a database crash or corruption, you could lose the logs and config settings which might make finding the problem impossible.
此外,如果配置文件是文本文件,则可以更轻松地将配置文件存储在源代码管理中,而不是存储在数据库中。相信我,这非常重要。您不想调试丢失导致错误的配置文件设置的应用程序。如果您遇到数据库崩溃或损坏,您可能会丢失日志和配置设置,这可能导致无法找到问题。
#8
0
If you're developing a small, static website, then I would agree with most of the points already made. However, if you have a website that allows for users to add content via the production site I would argue that putting configuration in the database complicates the deployment pipeline to the point that keeping it out of the database is preferable.
如果您正在开发一个小型的静态网站,那么我同意已经提出的大多数观点。但是,如果您有一个允许用户通过生产站点添加内容的网站,我认为将配置放入数据库会使部署管道变得复杂,以至于将其保留在数据库之外是可取的。
If you're trying to push an update from dev to production, clients are pushing content to production, and your config and content are both in the same database then you need to target only tables with configuration data to be overwritten. This 'can' be a trivial amount of extra work on your part, but it depends on the scale of the application and whether or not you're making use of someone elses code. Consider drupal sites. If users are adding content then for deployment you need to target specific database tables to be overwritten. Since drupal has several tables (none of which have config in their names) you'll need to do some research to figure out what can be overwritten and what can't. Now, what happens if something changes in drupal's database layout? Deployment pipeline could break and it's more extra work for you. What happens when you add a new plugin? More config tables, so changes in your deployment scripts are needed. More work for you. Should you eventually move on from this project, you would be expected to leave information with the new developer to explain what you've done with regards to these deployment issues. More work for you.
如果您正在尝试将更新从开发推送到生产,客户端正在将内容推送到生产环境,并且您的配置和内容都在同一个数据库中,那么您只需要定位具有要覆盖的配置数据的表。这个“可以”对你来说是一项微不足道的额外工作,但这取决于应用程序的规模以及你是否正在使用别人的代码。考虑drupal网站。如果用户正在添加内容然后进行部署,则需要将特定数据库表作为目标进行覆盖。由于drupal有几个表(没有一个表的名称配置),你需要做一些研究来弄清楚什么可以被覆盖,什么不可以。现在,如果drupal的数据库布局发生了变化,会发生什么?部署管道可能会中断,这对您来说是更多的额外工作。添加新插件会发生什么?更多配置表,因此需要更改部署脚本。为你做更多的工作。如果您最终离开此项目,您将需要与新开发人员分享信息,以解释您对这些部署问题所做的工作。为你做更多的工作。
Consider what would happen if the config wasn't in the database but in your applications directory structure instead. Save the config changes to git/svn/etc, push the changes to your server box and overwrite older files. DONE. The database will be touched less when you roll out changes, your config can be put under version control, and your application is now directly coupled with the configuration it makes use of (which makes sense). This is more valuable for moderate/large scale applications or applications which make use of pre-built components/frameworks that you have no control over, then for small scale applications. However, it works at all scales were as storing config in databases becomes more troublesome as your applications grow and your deployment pipeline becomes complicated.
考虑如果配置不在数据库中但在应用程序目录结构中会发生什么。将配置更改保存到git / svn / etc,将更改推送到服务器框并覆盖旧文件。 DONE。当您推出更改时,数据库将被触及较少,您的配置可以置于版本控制之下,并且您的应用程序现在直接与其使用的配置相关联(这是有道理的)。对于中等/大规模应用程序或使用您无法控制的预构建组件/框架的应用程序而言,这对于小规模应用程序更有价值。但是,它适用于所有规模,因为随着应用程序的增长和部署管道变得复杂,数据库中的存储配置变得更加麻烦。