What needs to be stored in a database (RDBMS)?

Question

Are there any recommendations / recommendations for choosing the data type in the database?

For example, it's ok to use a database for storage

• Application logs
• Configuration details (e.g. server IP addresses, etc.).
• System information (e.g. shell script names, scheduling information for batch jobs, batch job status, etc.).

I saw applications that use a database to store them. It is acceptable? What are the pros and cons of such a design?

Answer 1

To answer this question, we need to understand what a database repository is that is not available, for example, in a flat file repository.

• security - you can store things and be sure that updates, deletions and views will be controlled.
• audit - you can track who made changes and when
• distributed servers - if you have several application servers accessing the same database, you avoid storing the same data in several places

If this data is necessary for your data, it is recommended that you store it in a database.

Answer 2

Application logs

Although it is often useful to limit the data in the database to a specific time range (for example, dump / archive / condense for statistics of everything older than 3 months), the availability of logs in the database allows very quick and simple data analysis. 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 certain time? "SELECT * FROM logs WHERE Timestamp BETWEEN failure - 1 hour AND failure + 5 minutes."

Configuration details (e.g. server IP addresses, etc.)

It depends on the configuration details. Some yes, some no. Anything that is suitable for applications running on multiple clients (for example, websites) and that is likely to change frequently (for example, user preferences) should go to the database. For more or less static parameters, I prefer to use a configuration file.

System information (e.g. shell script names, scheduling information for batch jobs, batch job status, etc.)

I assume it is almost the same as the configuration data. If it changes: database. If it is static: config file. Shell scripts are usually static. Schedule information and status will change over time.

Answer 3

We have saved everything in the database in the last few projects, and this really helps in the transition from development to production, since the application itself has very few settings.

Access to the database can be useful (for example, Log4j), because it allows you to widely distribute access to the logs for testers and analysts.

I think it depends on your situation. Everything that is stored in the database adds a level of complexity to the system. It’s easier to read the file than to access the database to get the same information from the code. The likely rule is that if the thumb means that the larger the system, the more it should be stored in the database.

Answer 4

A small point: 99% of the time it is a terrible idea to save the configuration in the database. The configuration is too important to lose the connection to the database that went south: it should be 100% bullet proof.

Answer 5

RE: configuration data It might be nice to save the configuration data in a database in order to simplify their editing and track changes, but then pull it into the configuration file for a real program to read.

• Why does apache need to know anything about your database in order to be able to get its configuration?

• Why does your FTP server stop working when the database is down?

RE: Application Logs

As mentioned earlier, a database can make log analysis much easier, but I highly recommend that you consider the log-to-file-and-batch-import-later pattern.

Performance issues

Databases are great for getting random data bits and entering random data bits. Log data is generally not written randomly, but in a continuous stream of data, which is ideal for inserting a file one line at a time. You cannot outperform flat file performance when it comes to writing data. There are not many things that can break with a flat file. It also allows the database to focus on actual business work.

Then later you can collect all the registered data from the file, analyze it, perform any required post-processing (for example, search for host names from IP addresses) and place them in the database table. You do this as often as you see fit. For my website, I really don’t need to view visitor statistics from one minute to another, so I run the magazine at night. If you need updated information, you can also successfully start batch import every 60 seconds, but this will still be better than executing one additional INSERT statement for each specific business transaction (depending on how much you register, of course) .

Security

• How do you register a failed database connection if the database is your log engine?

• How do you investigate the cause of a system crash if the database went down earlier during the crash events?

So, I think you should think about when you need the log data in the database and why you need it.

Answer 6

One thing that has not been mentioned yet is that you drop things like application configuration in the database, you cannot easily put it under version control.

For example, some CMS like to force HTML templates into the database instead of files. I personally think this is a bad design. You cannot change any changes made to the templates, and, even worse, all you do is copy and paste from a real text editor into a wimpy text editor in a browser.

Bottom line? Ask yourself if this is what you want to version. If so, save it from the database. If not, sure put it in the database.

Answer 7

Focus on ease of use and maintenance. The only logs that I store in the database are placed there by triggers that exit the system due to the fact that this is easiest. But for everything else, searching and analyzing text magazines is faster and easier. If your application crashes, viewing a text configuration file is easier than searching in db , especially for new support ones . It is much easier for a new person to look at the app.properties file in the config/ directory than to know to look at the table in the database.

In addition, you can more easily store configuration files in the original control if they are text files than if they are in the database. And this is very important, believe me. You do not want to debug the application in which you lost the configuration file settings that caused the error. If you have a crash or database corruption, you can lose the logs and configuration settings, which can make it difficult to detect the problem.

Answer 8

If you are developing a small static website, I agree with most of the questions already made. However, if you have a website that allows users to add content through a production site, I would say that placing the configuration in the database complicates the deployment pipeline to the point that saving it from the database is preferable.

If you are trying to redirect the update from dev to production, clients are pushing the content to production, and your config and content are in the same database, then you need to target only tables with configuration data that will be overwritten. It may be a trivial amount of extra work on your part, but it depends on the size of the application and whether you use any elses code. Browse Drupal Sites. If users add content, then for deployment you need to target specific database tables to be overwritten. Since drupal has several tables (none of which have a configuration in their names), you need to do some research to find out what can be rewritten and what cannot. Now, what happens if something changes in the layout of the drupal database? The deployment pipeline might break, and for you, this will work more. What happens when you add a new plugin? Additional configuration tables, so changes to deployment scenarios are needed. More work for you. If you end up moving from this project, you should leave the information with the new developer to explain what you did about these deployment issues. More work for you.

Think about what happens if the configuration was not in the database but in the application directory structure. Save the configuration changes to git / svn / etc, click on the changes in the server field and overwrite the old files. MADE. The database will be less affected when the changes are deployed, your configuration can be installed under version control, and your application is now directly connected to the configuration in which it is used (which makes sense). This is more valuable for medium / large-scale applications or applications that use pre-created components / frameworks that you do not control, and then for small applications. However, it works at all scales, as saving configurations in databases becomes more difficult as your applications grow, and the deployment pipeline becomes complex.