Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snuoq-006ugJ-1a for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 06:57:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1snuop-007yqB-Fp for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 06:57:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snuoo-007yq1-UL for pgsql-general@lists.postgresql.org; Tue, 10 Sep 2024 06:57:43 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1snuol-000QKa-L5 for pgsql-general@postgresql.org; Tue, 10 Sep 2024 06:57:41 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-42cb2191107so17677015e9.1 for ; Mon, 09 Sep 2024 23:57:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=leisi.net; s=google; t=1725951458; x=1726556258; darn=postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=+M4rW3jLwvFnCbxZR8AlGhWJbRDu5cAiCgiFSoxUEH8=; b=hT90WGQDOjZAblNNR88MarT4e5d2w31Ex3tLF1Ix/IKcuoZSR1TkG3liF5fl8ir+tq xGSjXg9iFaslbpdmtS3Lg2RcUPMQIP7Iq4xIwXmRSztXTqEtc5PjpmIhTDuGm7tshMxN QOr8Kq9UENihmDa/+H2gi/7rYIHrcnkvTFok4= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725951458; x=1726556258; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=+M4rW3jLwvFnCbxZR8AlGhWJbRDu5cAiCgiFSoxUEH8=; b=r67+Btp0NcsWQZ07xmChB2bvVzPad9s+USa3N7ah2ZHoU/R3o/tVC4atHQEGSxi01W TK84fViPbqt0NOqSCpvrYyxbDezfkCl7Ug/KhIiAnjKYmcbfrEDI5nCkBsnJi/OoPsJF O/htXaym/PuB98lnv/Jb8oYYVTOjZHF0sZc3mJrFttr/cTfHrq0k2piH5Seg3s+LJSO7 sGtca3Cldk87x9v6gh0ioOvnaolyu8jUm5EAyVyrl5XMuLJOz0LKD1ywSTnwImw9F193 Lo5lGpwnVyNznsudhsx501/YoRo+knKZLsaRE14DcdHO3p4NJ9itootvq7aTLIHUJaBB MKVA== X-Gm-Message-State: AOJu0YwG+PAj4YTKZLlkCVzmJfywApFT2ZOCYwoDLVLtUEmxrRcpGb78 Xu7uHt1hc3pCKRERD+aWmx4xXoHoJeSg4BHgdtMJYZzzoy7YsyCyMzT5xi8cVK6KeboH3gg45ik = X-Google-Smtp-Source: AGHT+IEMF2rIoiXJqwNxPQRxzKL4ash9CxqEmW6uR2bN1rnQCtNQrJRCTVKYzprler+owBRnqBkL/w== X-Received: by 2002:a05:600c:45cb:b0:428:1608:831e with SMTP id 5b1f17b1804b1-42cad76a0b5mr73236435e9.22.1725951457450; Mon, 09 Sep 2024 23:57:37 -0700 (PDT) Received: from smtpclient.apple ([2a02:169:ce0e:0:7934:af45:eba7:fc50]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-37895675c56sm8083854f8f.57.2024.09.09.23.57.36 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 09 Sep 2024 23:57:37 -0700 (PDT) From: Matthias Leisi Content-Type: multipart/alternative; boundary="Apple-Mail=_E52801E7-F84F-4567-9DF2-5A89DDA8D6A0" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\)) Subject: Database schema for "custom fields" Message-Id: Date: Tue, 10 Sep 2024 08:57:26 +0200 To: pgsql-general@postgresql.org X-Mailer: Apple Mail (2.3774.600.62) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_E52801E7-F84F-4567-9DF2-5A89DDA8D6A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I=E2=80=99m looking for input on a database design question.=20 Suppose you have an application that allows the user to add some kind of = field to the application (=E2=80=9Ecustom fields=E2=80=9C, =E2=80=9Euser = defined fields=E2=80=9C, =E2=80=9Eextended fields=E2=80=9C, =E2=80=A6), = which could be of different types (eg string, int, bool, date, array of = , =E2=80=A6), 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 (=E2=80=9Esearch in custom field=E2=80=9C = or similar). It=E2=80=99s 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 =E2=80=9Ecust= om fields table". Drawback: needs DDL privileges for the application = user, makes future schema updates potentially more difficult. Pro: = =E2=80=9Eproper=E2=80=9C DB-based approach, can use all features of the = DB. 2) Use a text-based or JSON field to store the =E2=80=9Eextended=E2=80=9C = data. Drawback: type validation, query efficiency?. Pro: Very flexible? 3) Use a =E2=80=9Edata table=E2=80=9C with one column per potential type = (fieldid, valstring, valint, valbool, =E2=80=A6). Drawback: complex to = query, waste of storage? Pro: use all DB features on =E2=80=9Etrue=E2=80=9C= columns, but without needing DDL privileges. Are these the right drawbacks and pro arguments? Do you see other = options? Thanks for your insights, =E2=80=94 Matthias --=20 Matthias Leisi Katzenr=C3=BCtistrasse 68, 8153 R=C3=BCmlang Mobile +41 79 377 04 43 matthias@leisi.net --Apple-Mail=_E52801E7-F84F-4567-9DF2-5A89DDA8D6A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
I=E2=80=99m= 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 = (=E2=80=9Ecustom fields=E2=80=9C, =E2=80=9Euser defined fields=E2=80=9C, = =E2=80=9Eextended fields=E2=80=9C, =E2=80=A6), which could be of = different types (eg string, int, bool, date, array of <any other = type>, =E2=80=A6), 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 = (=E2=80=9Esearch in custom field=E2=80=9C or similar). It=E2=80=99s 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 =E2=80=9Ecustom fields table". Drawback: = needs DDL privileges for the application user, makes future schema = updates potentially more difficult. Pro: =E2=80=9Eproper=E2=80=9C = DB-based approach, can use all features of the = DB.

2) Use a text-based or JSON field to store = the =E2=80=9Eextended=E2=80=9C data. Drawback: type validation, query = efficiency?. Pro: Very flexible?

3) Use a = =E2=80=9Edata table=E2=80=9C with one column per potential type = (fieldid, valstring, valint, valbool, =E2=80=A6). Drawback: complex to = query, waste of storage? Pro: use all DB features on =E2=80=9Etrue=E2=80=9C= columns, but without needing DDL = privileges.

Are these the right drawbacks and = pro arguments? Do you see other options?

Thanks = for your insights,
=E2=80=94 = Matthias

-- 
Matthias = Leisi
Katzenr=C3=BCtistrasse 68, 8153 R=C3=BCmlang
Mobile +41 79 = 377 04 = 43
matthias@leisi.net

= --Apple-Mail=_E52801E7-F84F-4567-9DF2-5A89DDA8D6A0--