Re: Database schema for "custom fields"

Hi,
There is not a properly defined solution but you can try the
Entity-Attribute-Value (EAV) Model. This is an alternative approach, where
a separate table is used to store custom fields as attributes for each
record. New fields can be added without altering the schema. There will be
no need for DDL changes. There might be some cons as you might need
multiple joins to retrieve all fields for a given record.

On Tue, 10 Sept 2024 at 11:57, Matthias Leisi <matthias(at)leisi(dot)net> wrote:

> I’m looking for input on a database design question.
>
> Suppose you have an application that allows the user to add some kind of
> field to the application („custom fields“, „user defined fields“, „extended
> fields“, …), which could be of different types (eg string, int, bool, date,
> array of <any other type>, …), and which would have some additional
> properties (like a display name or description, or some access control
> flags).
>
> The application would need to be able to do CRUD on field content, and
> potentially use them in queries („search in custom field“ or similar). It’s
> not expected to be a high-transaction database, and not go beyond ~100k
> records. Data integrity is more important than performance.
>
>
> How would you design this from a DB point of view? I see a few options,
> but all have some drawbacks:
>
> 1) Allow the application to add actual database columns to a „custom
> fields table". Drawback: needs DDL privileges for the application user,
> makes future schema updates potentially more difficult. Pro: „proper“
> DB-based approach, can use all features of the DB.
>
> 2) Use a text-based or JSON field to store the „extended“ data. Drawback:
> type validation, query efficiency?. Pro: Very flexible?
>
> 3) Use a „data table“ with one column per potential type (fieldid,
> valstring, valint, valbool, …). Drawback: complex to query, waste of
> storage? Pro: use all DB features on „true“ columns, but without needing
> DDL privileges.
>
> Are these the right drawbacks and pro arguments? Do you see other options?
>
> Thanks for your insights,
> — Matthias
>
> --
> Matthias Leisi
> Katzenrütistrasse 68, 8153 Rümlang
> Mobile +41 79 377 04 43
> matthias(at)leisi(dot)net
>
>