DB table "settings" with key-value data and how to improve it?

Questions / Help
,
1

I have a table “settings” which will be used for all kinds of settings that need to be edited via UI.

It’s a simple “key (string) - value (string)” one. There’ll be a most 100 records in it. They’ll be updated in place.

  • I expect that some keys will reach 50…70, even up to 100 characters – this_setting_which_i_need_to_be_thing_long

  • Each key is UNIQUE.

  • Some values will in fact be string, others boolean, others decimals. The column “value” is string, nonetheless.

How are these kinds of settings normally dealt with?

For instance:

Should I instead have 3-4 columns for the “value”: “string_value”, “decimal/integer_value”, “boolean_value”? To then treat them individually, depending on the key.

Is there a way to shorten the key yet preserve the purpose of each one? If I shorten it by shortening the words in it, I might eventually forget what it’s for. Or it won’t be clear immediatelly. Can hashing – md5(key) – help with this somehow?

2

I think for the sizes of things you’re talking about it’s going to be hard to “get it wrong”. ~100 records, candidate keys of ~100 characters… none of this is big in a way that you’re going to impact your run time performance such that you’d worry about it.

When I do this, I have a preference for typed data in the storage of the configuration values, but I’ve seen simple configurations handled as text in the database to keep the table simple and then cast in the application as needed… again not my style, but I’ve certainly seen it in practice more than once.

Here’s some abbreviated code from something similar I do in my current application:

CREATE TABLE ms_syst_data.syst_settings
(
     id
        uuid
        NOT NULL DEFAULT uuid_generate_v4( )
        CONSTRAINT syst_settings_pk PRIMARY KEY
    ,internal_name
        text
        NOT NULL
        CONSTRAINT syst_settings_internal_name_udx UNIQUE
    ,display_name
        text
        NOT NULL
        CONSTRAINT syst_settings_display_name_udx UNIQUE
    ,description
        text
        NOT NULL
    ,setting_flag
        boolean
    ,setting_integer
        bigint
    ,setting_decimal
        numeric
    ,setting_interval
        interval
    ,setting_date
        date
    ,setting_timestamp
        timestamptz
    ,setting_json
        jsonb
    ,setting_text
        text
    ,setting_blob
        bytea
);

COMMENT ON
    TABLE ms_syst_data.syst_settings IS
$DOC$Configuration data which establishes application behaviors, defaults, and
provides a reference center to interested application functionality.$DOC$;

COMMENT ON
    COLUMN ms_syst_data.syst_settings.id IS
$DOC$The record's primary key.  The definitive identifier of the record in the
system.$DOC$;

COMMENT ON
    COLUMN ms_syst_data.syst_settings.internal_name IS
$DOC$A candidate key useful for programmatic references to individual records.$DOC$;

COMMENT ON
    COLUMN ms_syst_data.syst_settings.display_name IS
$DOC$A friendly name and candidate key for the record suitable for use in user
interfaces.$DOC$;

In this example, I’m completely ignoring certain costs related to the extra indexing and having so many typed fields (the actual table has more than I show here). But I feel I can get away with not worrying about that because, like in your scenario, I’m unlikely to have more than 1000 records in the table, update rates will be low, and again, I’d rather have more strongly expressed data in the database even though I could be much more space and probably time efficient by not typing the actual configuration data: at this size I just won’t worry about it until I have reason to start to compromise for performance.

The short of it is, I don’t see a real problem with your handling ideas and I’d shoot for some good combination of simplicity and clarity without the burden of worrying about performance related issues at this point.

3 Likes
3

That’s helpful.

What does the full definition of the table look like, though?

4
1 Like
5

What’s the type in Ecto for interval?

6

I created an Ecto custom type (Ecto.Type — Ecto v3.10.3) to handle interval.

To be fair, I think most of the actual work was already done in Postgrex.Interval and there might have been some work already existing inside of Ecto (been a long time since I dealt with this); so the real heavy lifting was done elsewhere and I just completed the circle as it were…

1 Like