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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1snwuK-0078cP-LJ
	for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 09:11:33 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1snwuJ-00A5uJ-W2
	for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 09:11:31 +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 <hjp-pgsql@hjp.at>)
	id 1snwuJ-00A5tj-Ih
	for pgsql-general@lists.postgresql.org; Tue, 10 Sep 2024 09:11:31 +0000
Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at)
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <hjp-pgsql@hjp.at>)
	id 1snwuC-000RTI-Ag
	for pgsql-general@lists.postgresql.org; Tue, 10 Sep 2024 09:11:29 +0000
Received: by rorschach.hjp.at (Postfix, from userid 1000)
	id 630B9117F8; Tue, 10 Sep 2024 11:11:21 +0200 (CEST)
Date: Tue, 10 Sep 2024 11:11:21 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Database schema for "custom fields"
Message-ID: <20240910091121.2pgurwdqwse4xm4j@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <A5EB2191-62F9-44D3-8488-9C67DC033453@leisi.net>
 <CAPnRvGuxXMBPy0nLks_=wcRQhcAeXj-=kKHbEAsOMSs6MsmWZA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="oizqupqmsymrkduv"
Content-Disposition: inline
In-Reply-To: <CAPnRvGuxXMBPy0nLks_=wcRQhcAeXj-=kKHbEAsOMSs6MsmWZA@mail.gmail.com>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/20240910091121.2pgurwdqwse4xm4j%40hjp.at>
Precedence: bulk


--oizqupqmsymrkduv
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-10 12:08:14 +0500, Muhammad Usman Khan wrote:
> There is not a properly defined=C2=A0solution but you can try the
> Entity-Attribute-Value (EAV) Model. This is an alternative approach, wher=
e a
> separate table is used to store custom fields as attributes for each reco=
rd.
> New fields can be added without altering the schema. There will be no nee=
d for
> DDL changes. There might be some cons as you might need multiple joins to
> retrieve all fields for a given record.

I think this is essentially Matthias' option 3:

> On Tue, 10 Sept 2024 at 11:57, Matthias Leisi <matthias@leisi.net> wrote:
>=20
>     I=E2=80=99m looking for input on a database design question.=C2=A0
>=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=9Eu=
ser defined fields=E2=80=9C, =E2=80=9Eextended
>     fields=E2=80=9C, =E2=80=A6), which could be of different types (eg st=
ring, int, bool, date,
>     array of <any other type>, =E2=80=A6), and which would have some addi=
tional
>     properties (like a display name or description, or some access control
>     flags).
[...]
>     How would you design this from a DB point of view? I see a few option=
s, but
>     all have some drawbacks:
[...]
>     3) Use a =E2=80=9Edata table=E2=80=9C with one column per potential t=
ype (fieldid,
>     valstring, valint, valbool, =E2=80=A6). Drawback: complex to query, w=
aste 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 opt=
ions?

I pretty much agree with your analysis. I used to use your option 3 a
lot, mostly because I thought that the schema should be fixed at design
time and not changed by the application. I'm less dogmatic now and would
probably lean more to your option 1 (let the application add columns to
an "extension table").

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--oizqupqmsymrkduv
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbgDTIACgkQ8g5IURL+
KF1h1g//SVfV/+whLPv4Xfyz1zRSlRO1WM9rpk1ZuRYLL3OcNMkCuPsOLXQ3SeIO
cn84FQhjLy1jA72gwZ3wf/2xqu8zyI5pr6HcowCa93DTOhEhC/g4eiwf9vBBFjBO
5icNkR6ZFz1m5cC6ss2RSBE/Url1iDTH2xjCk/C1WJ0hHFSc4iWteUMKqAoK2Hlp
YJZU7RUaKIAXtXK37arI+PzNMhr8DwMul42wQH/Gfjk/dAVpp0pfRzJYRDoxzF33
/54JHQ5LIb2br8xW8RoO6VKkQBrvIxN+Klyv0khMmKexYAoSXgUkxWEZYW5mmjc7
11h/8MEqWVj3q4ZJNVw/ZLNgOxHODL2Mrlf22Ab5RX+1urGYsAsNiX2j5Dg2f+3N
xEfS8Gcg2v/0s4eOVtL8hpjQelH+yGPoK+qEodAPQM4d1y/+NezoYRV+uqH41PWm
IiEU+QwsnJ87uXOeJ038Gql3F3lcj22T+trBuS61n4q3Uv4CBGu3qdPelJCr52ic
W3Gr6Xf5INrOI4wlz57WUZ1ZDo+kN1S/0OngOt4vGJ0Fqaqxd8rDQeDxY76noyHo
FFlVfNaTYf5Eun/0Chg05SHL8WwHQNf65w1Q5Qpymd+CKj4OpDPcMtFnwTPlwUde
FJyqeAsRBwhizcCnom64Y+Q2nDY2bNiFNRUA0JKne+yNV8q7PgE=
=6YuE
-----END PGP SIGNATURE-----

--oizqupqmsymrkduv--