-1

Relational database is mostly used for storing editable user data.

But how about storing non-editable lists of information? Possible examples are:

  • GUI-related: main menu items, selectbox options, list of links, set of UI colors
  • application logic: list of user types and their privileges, default table view configuration (default applied filters, columns...), internal application config

We usually don't call these items "data", they are rather part of the application itself and their definitions are hard-coded directly in the source code. It is however technically possible to e.g. store menu items in a database table and construct the menu by running SQL query and iterating through the rows.

Advantages of storing information in db. include:
  • list of items stored in a database table has solid structure and can be manipulated with SQL - queried, sorted and filtered
  • items can be edited directly in the database, you don't have to commit to Git and run CI/CD process every time an edit happens, you can even build special admin interface (possibly web-based) above the database so even non-programmers can edit the items
  • if the list of items is large, it is effective to store it in the database and share the data across multiple app instances rather than have it separately in the source code of every app instance
Disadvantages of storing information in db. include:
  • performance overhead when querying the database
  • when modifying the information stored in a database, you must use database migrations to keep record of these changes, which is much more inconvenient than modifying only the source code where you can easily use Git to keep versions history and sync changes between environments
  • it is inconvenient for programmer to work with both source code and database data all the time, it is more convenient to have all information in the source code only
  • in contrast to source code, with items stored in database you can't freely use programming language features like storing native programming language objects, function callbacks or constants/variables in the database (e.g. for displaying/hiding menu item based on some boolean variable or constant etc.)
  • storing data in database is more insecure than storing it in source code, as everybody with credentials to the database can edit the items and possibly ruin the application, moreover it is hard to find out who and when has made the editing
  • if you develop frontend app and some UI parts (like menu items) are stored in database, your frontend needs to make an API call to get those items and subsequently construct the UI

I have come across an opinion that everything which is a structured list of information (even non-editable) should be stored in a database. Given the reasons above, would you consider this to be a good concept in respect to:

  • code clarity
  • performance
  • flexibility
  • common programming techniques (we don't want programmers to experience "WTF moment" when dealing with the project)
Improve this question
5
  • 3
    "Relation database is mostly used for storing editable user data." Disagree - huge amounts of non-editable, non-user data is stored in relational databases.
    – Philip Kendall
    May 18 at 10:50
  • @PhilipKendall I agree there is non-user, mostly static data being stored in database (like list of countries, list of languages etc). My question was rather about storing possibly everything in the app in db., even if it's not data in typical point of view. E.g. application menu with several items and some behaviour - I can use in-memory array of items or store it in db.
    – user14967413
    May 18 at 12:19
  • Your question at the moment is surprisingly both too broad ("everything which is a structured list of information (even non-editable) should be stored in a database", your initial assertion) and too narrow in the you seem to be thinking only about configuration data for a small-scale app. What's the actual practical problem you're trying to solve here? There are no absolutes in software engineering, everything is trade-offs based on your specific use case - and the same applies to synonyms such as "best practice" and "most common".
    – Philip Kendall
    May 18 at 12:28
  • For me the problem is about database overuse. Imagine a common CRUD web app. There's a menu in pure HTML, some basic config written in JSON, list of user types in an array etc. When adopting the idea "I should store as much as possible in db.", I will move the menu structure to db., will move config values to db., in the end everything which resembles tabular structure will be moved to db. The basic argument for this is "we can easily edit the data in the db., without modifying source". But for programmer it is much better to see the structures directly in the source and use Git to version it.
    – user14967413
    May 18 at 13:16
  • 1
    You are configuring your application. How much you need to separate your actual code from your configuration, depends on when it can change and who needs to do it. One situation may require a string value embedded in the code, another full Active Directory integration.
    – Thorbjørn Ravn Andersen
    May 20 at 18:56

2 Answers 2

Reset to default
2

Generally no, you shouldn't.

I think the things you list as advantages are not real advantages:

  • "solid structure and can be manipulated with SQL".

    General purpose programming languages all allow you to create documented and consistent structures. Depending on the language you may want to add a static analysis tool to check that intended structure is followed universally.

    SQL manipulation may be useful for very large datasets, but in general the manipulation tools of your programming language, with libraries if necessary, should be sufficient.

  • "edited directly in the database, you don't have to commit to Git and run CI/CD"

    This is not a good thing. The automated checks people create as part of CI/CD delivery pipelines are extremely valuable. It's good if we can use them to check our application works with a new version of the data set. With a good pipeline it should be possible to any changes in under an hour, ideally under 15 minutes. There is a caveat though - if the people who would be making the changes are different to the people who program the application, or the data needs to be change much more often than the other application code then it may be better to store it in the database. I would say that takes out of the non-editable category.

  • share the data across multiple app instances

    This is reasonable if the data is genuinely very large. Maybe it would apply to something like a an IP address to geolocation database - probably not to anything that would be handwritten or hand-compiled by your development team.

Improve this answer
1

If you extrapolate further, you could store all of your code and binaries in the database and fetch it at runtime.

Databases store information, and unless you're doing something like storing users' interactions with the GUI elements for analytic purposes, then the best tool for storing static code data is in code in version control.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.