Software Engineering
database organization backend settings
Updated Tue, 14 Jun 2022 13:53:18 GMT

Saving flags in the database without relationships to other tables

I have an admin panel which can set a lot of options which will affect some other services drastically. For example I got one that if I create a different category I can just insert it in the Category table and then the front-end will be able to retrieve it.

But I'm almost totally lost with what do I do with simple flags and how do I save them

I will show few examples:

  1. "2FA required" - which affects the other service and force every user to enable 2FA on registration.
  2. "Manual transactions" - Whenever a user deposits or withdrawas, the transaction requires manual admin\moderator check via the admin panel

I could give more examples but I think it's enough. These are basically flags which state I need to save in some way and access on end-points.

But I feel like saving flags in the database without relationships with other tables is not the completely right approach.


There is nothing wrong in having tables with no relationships.

The choice of a database in order to store those settings is perfectly fine and quite common. The only issue you might encounter is performance, but basic caching at server level would quickly solve this.

A usual problem is what types of columns should you use for such table if you're relying on a RDBMS. For global settings, you may have two columns: a key (a variable length Unicode string with a relatively short maximum length; primary key) and a value. Choosing a string of unlimited length may be a wise choice at the beginning, given that (1) it would prevent you from storing binary data (and it is quite rare to have the need to store binary data as settings), and (2) it won't be optimal when storing numbers. But that's a good start, and you can add more tables later on based on an actual need. If many options are in fact structured data with variable types, you may prefer an XML or JSON type instead (if the database supports those types).

Note that it may be useful to have per-user options instead of global ones. For instance, instead of having a global 2FA required, you may prefer forcing 2FA on only some users or groups of users. Combined with feature toggles, this gives you an opportunity to enable some features for some users only to test new ones before making them global. So, for instance, you may force 2FA for all administrators and testers, as well as 5% of your users to test how well this practice is accepted. Then, if you see a sudden drop in the number of customers among those 5%, you keep 2FA requirement for administrators only, all this, with no need to recompile and redeploy your application.

Comments (2)

  • +0 – I see. So, do I just make a table with 2 columns: key for an option and flag and then it's enough? As for caching I think I could just get it once the server is started and then don't access the database for that — Aug 07, 2019 at 17:56  
  • +0 – @DarkZ: yes, unless you want them to be user/group specific, in which case you need a third column. — Aug 07, 2019 at 18:05  

External Links

External links referenced by this document: