public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthias Leisi <[email protected]>
To: [email protected]
Subject: Database schema for "custom fields"
Date: Tue, 10 Sep 2024 08:57:26 +0200
Message-ID: <[email protected]> (raw)

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Database schema for "custom fields"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox