We have a table design in our database to dynamically store settings:
You wouldn't need to understand every column in the table but basically tbl_Setting defines all possible settings and tbl_SettingValue assigns a setting with a concrete value to an entity (user, project etc.). SETVL_T_Value contains a serialized value ("true" for a boolean, "2019-01-01" for a date etc.) This gives us a high degree of flexbility.
Now we want to make these settings available via API. The API is used by other programmers who do not have direct access to the database and are not familiar with the database schema.
Generally we use DTOs (Data Transfer Objects) to abstract the database schema and return it in a simplified form to the API user.
We see two options how to make the settings tables available to the API user:
1. Static DTO
A DTO which contains a property for each setting. So for example:
public bool SowDescriptionForProject;
public bool CanCreateAppointments;
Advantage: It is easier to use for the API user.
Disadvantage: We give up our flexibility because the API has to be adjusted for every new setting.
2. Dynamic DTO
Return a List of settings from the API
List<Settings> settings;
public class Settings
{
string name;
string value;
}
Advantages: New settings are immediately available via the API without any adjustments.
Disadvantages:
- The API user must deserialize the value itself
- There are settings that are based on a selection list. Each selection option has a unique integer value (1,2,3,4 etc.) which is then stored in the SETVL_T_Value field. The user of the API must know what each value means. It is not directly obvious and must be well documented. I think this is the biggest disadvantage of this solution.
My question:
What do you think is the best solution? Is there a best practice?