How to reduce the number of reference tables with three or four rows?

Question

Database design

Example:

1. There is a table "Orders". Many table fields are foreign keys to other tables.
   
2. These "other tables" most often have 3-4 (sometimes more) lines of the "key-value" type
   
3. All these microscopic reference tables are very constant or have never changed at all.
   

Problem: there are much more such directories in the current database than the tables for which they were created.
Q: How do you store related data like this? How should it be done, and how not?

Now it turns out a zoo of auxiliary tables, when the main tables - a dozen and a half, and directories - over a hundred. For those who are interested, for the order table, for example, there may be such directories:

1. Order statuses (open/released/closed, etc.)
   
2. Order assembly statuses (pending assembly, assembly started, assembly checked, assembly completed)
   
3. Order payment formula (there are many options)
   
4. Payment fund (own funds, federal budget, territorial, etc.))
   
5. Internal client (one of the possible internal LLCs)
   
6. Responsible officer
   
7. Territorial sector
   
8. Direction
   
9. ...and 15 more tables
   

Bottom line based on the responses received:

• In one project, we leave it as it is - well, let it be that there are so many tables.
  
• In another project, let's switch to the enum data type.
  

Thanks to all!

Answer 1

there are several approaches, I touched each of them and they all have the right to exist
1. leave it as it is, I recommend that there be 100500 lookup tables (if there are no problems with names, well, that's fine), fk-indexes will connect all this and analysis tools databases will help to work with this (automatic query builder with a mouse-like interface) and the database will make sure that the identifiers of the directories are not contradictory
I can’t think of any shortcomings, except for maybe a very overgrown structure, once upon a time I heard that some databases start to work worse with an increase in the number of tables, but as I understand it, if there are tens of thousands of them and not tens and hundreds .. maybe backup such a database or its replication will be a little slower or even worse, organizational replication can be configured to not send out ddl modifications, in which case the creation of a new directory will also task admins, which is undesirable.
2. the opposite, using one key-value table for several heterogeneous directories (id, value, table_name), in 99% of cases you can use one sequence (one sequence of identifiers), but you don’t have to have different directories start counting their rows from one and the same number 1.
The disadvantage is that the database will no longer be able to control that you are using a consistent identifier (you can specify a number from another in the record of one directory), although it will also be impossible to specify a non-existent one (and delete cascade will work), and there will be no convenient sql auto-generation. The structure will be simpler, also the interface for editing such directories can be one instead of a bunch of forms and it will be much easier to add new directories (although from a development point of view there is not much difference whether you wrote one insert or create table before that)
3. do not store directories in the database at all; the backend is responsible for the value of the directories (stored in static files or in the code), the most dibly approach, editing such directories is most likely only by hand in the configs / source codes. Disadvantages - the database will not know anything about the values ​​​​at all and will not be able to either control or help with requests.
But this will allow you to take some load off the database, make the structure simpler and allow you to transfer the management of lookup values ​​to the mercy of the ide editor (if you store the lookup values ​​in the code , you can give the values ​​\u200b\u200bvariable names, since in the code you often have to start one way or another )
Personally, I didn’t use the third approach in its pure form, but I had a set of scripts that generated code from reference books in the database with initialization of constants and their names for the application (the reference books themselves could not be edited by the application), and it was tempting to translate this part of the database from ' operational' into 'for the developer', since the directory is something that is responsible for displaying information, but not for business processes (well, just like language files for an application, do not store interface line translations in the database either, well, directories will be able to work as part of this interface and may also require translation).