Design of DTO-Objects for an API with dynamic table design

Question

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?

Answer 1

Taking the static DTO route puts an undue burden on your development team, i.e. by having them redevelop and redeploy for every minor data alteration. You should sense that this is infeasible as a life cycle strategy.

The dynamic DTO route is by far the preferred one.

The API user must deserialize the value itself

Any consumer of a dynamic data set must, by definition of what a dynamic data set is, be able to handle data of a format that has not been predefined. Any attempt to expect certain values is inherently met with having to deal with the possibility that the expected value simply is not there.

This is true of your code and that of any other consumer.

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.

Okay, so you fetch your dynamic data from the database. You get an integer value. How do you know that this is based on a closed set of values? How do you know what the set of possible values is, and what the meaning behind each value is?

This doesn't quite make sense. If your own code knows all of these things, then you can pass this along to the consumer. If your own code doesn't know these things, then why would you task yourself with being the one who supposedly has to inform the API consumer about information you don't have yourself?

Since you're dealing with dynamic data, you can't possibly have predetermined that this int is "obviously" part of this closed set. The only explanation that makes any sense is that your dynamic data itself contains additional information, e.g. the name of the enum type that the int value represents.

Whatever information that led you to realize that this is a closed set of values, nothing is preventing you from adding this information to your result object, e.g. by adding a metadata property that contains further description (range of values, description per value, ...)

As this is metadata, you can basically structure it however you want. The world is your oyster.

Answer 2

You should design the API without constraining it based on how you choose to persist them. Instead make the API easily usable and foolproof.

Here is an article I wrote some years back about designing an API for configuration. This design is more like your dynamic version, except it solves the serialization/deserialization explicitly. It was designed to be easy enough to be used inline. Here are some of the requirements/assumptions used in the article (use your own for your own design):

• Should be easy enough to use inline
• It should be independent of how it is persisted (if at all)
• It should be typed, i.e. bool, int, string, etc. values.
• Types should be extendable too, i.e. duration, date, url, whatever.
• Types should not be specified where it is used, but where it is defined.
• Always have defaults, avoid nulls, without stepping on usability.

And so on, have a look at the article. I end up with something like this (as the core):

public interface Key<T> {
   String getName();
 
   T getDefault();
 
   T deserialize(String serializedValue);
}
 
public interface Configuration {
   boolean isSet(Key<?> key);
 
   <T> T get(Key<T> key);
}

An additional advice: Do not push work on your users. If you know something, like the enum thing, resolve it, provide the appropriate type to be used, etc. Do not just throw data over the fence. Give your users something that can be readily used and can not be misused just because a user does not know some obscure rule about some enum.