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 1snuzJ-006wEV-27 for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 07:08:34 +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 1snuzI-0088Q9-JG for pgsql-general@arkaria.postgresql.org; Tue, 10 Sep 2024 07:08:32 +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 1snuzH-0088Ph-UV for pgsql-general@lists.postgresql.org; Tue, 10 Sep 2024 07:08:32 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1snuzD-000QVO-TU for pgsql-general@postgresql.org; Tue, 10 Sep 2024 07:08:30 +0000 Received: by mail-pj1-x1034.google.com with SMTP id 98e67ed59e1d1-2d885019558so3590045a91.2 for ; Tue, 10 Sep 2024 00:08:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1725952106; x=1726556906; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=5++AFdBZXrg0G7IMDf6448dczDR7u5UpyeolyrQsmgU=; b=0BzlP47KhaLviQXmnViEtSbuyKxD6BRQPznq6R72+H1gifA+ESTPxuVUAkN8K8PJkA zHAqWp4oDahgoNnanoqNya6b4//6SDrJtv5olJ+567JLboW8kRP3mQniozivzJmgruBX dzWqeRmg24a9yJuB8PB2An5gob43tdC8y6aUobGcJmZbijO5bs+9fyZIM84uUb5CkW/+ X/7lE0xlr4stuJdd5qSazNASxUucKl+r1bLKRV9GSP9EtGWvB7uzTP6+jEH3yCp9/Dlg aXbx9gOkEpvAISPvAxK0bwyIANSzMa4D7QUjrjNVd8Vztw5MC0z1MWQ0RevNfBy867IC bp/A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725952106; x=1726556906; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=5++AFdBZXrg0G7IMDf6448dczDR7u5UpyeolyrQsmgU=; b=e5BHm8P6CtAAm7WHMv1/2rILmMy5j8p/4c9/QcnkR141AuL9RBXYPSWwuFtpqoTBCS tPXL0dLFv1HVmPgyj8Lo4oZ20CLw0VwWGZC2YF5KUg00BgAlCaMODCBQ+Mg7BVzZ7tzx HN6UgJ+rzKZjbeCOuH3BktDrw2JkZI7hz8XWKcBKXxrhJNut7hBOlibsSu8xPUsuXIjD Z7s1eLlaXP2RuHxeWnm/9Quxx9Zb/bLQTiyMu1JqAZ4BsGGlWkR5oM6VuVSj6WbeMT1c tMp/nwifx8k0L3KtdFvYnBZPqBHfEYtPpmnzX9E1fSw2A0kP+pswupAOPM8czZCnQB+S UGCQ== X-Gm-Message-State: AOJu0YzbgTJ+zISu1I5vfXlajksm5oKYY94POEoJdoF3zts+En5SHHSh ru5ert/EUh5kPzuBlbaKtVfhbKGPH1L4ANv69J1JEdlAX6f4Vi3WdTIcXwYmDdk2cVAj2+q1usO QFt7KtZt1u/rYlrXrEDs9BiYReOIJBwOK35U+j++o4mv88OAaJoQzUg== X-Google-Smtp-Source: AGHT+IHSXPVZEfMIK8+3NI7yeSAr1ZVYz+Q9cgkCGW1EWVbjy2n+bxeDkfYNI+4ZsAQbJoBa/UFb2gE2+o4POYlSGSc= X-Received: by 2002:a17:90b:383:b0:2d6:1c0f:fea6 with SMTP id 98e67ed59e1d1-2dad50d1bf2mr13520199a91.11.1725952106330; Tue, 10 Sep 2024 00:08:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Usman Khan Date: Tue, 10 Sep 2024 12:08:14 +0500 Message-ID: Subject: Re: Database schema for "custom fields" To: Matthias Leisi Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000032992f0621be8a71" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000032992f0621be8a71 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrote: > 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 , =E2=80=A6), and which would have some addition= al > 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=9Ecus= tom > fields table". Drawback: needs DDL privileges for the application user, > makes future schema updates potentially more difficult. Pro: =E2=80=9Epro= per=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 > > --00000000000032992f0621be8a71 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
There is not a proper= ly defined=C2=A0solution but you can try the Entity-Attribute-Value (EAV) M= odel. This is an alternative approach, where a separate table is used to st= ore 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 Tu= e, 10 Sept 2024 at 11:57, Matthias Leisi <matthias@leisi.net> wrote:
I=E2=80=99m looking for input on a data= base design question.=C2=A0

Suppose you have an ap= plication 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 potenti= ally use them in queries (=E2=80=9Esearch in custom field=E2=80=9C or simil= ar). It=E2=80=99s not expected to be a high-transaction database, and not g= o beyond ~100k records. Data integrity is more important than performance.<= /div>


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 th= e application user, makes future schema updates potentially more difficult.= Pro: =E2=80=9Eproper=E2=80=9C DB-based approach, can use all features of t= he DB.

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

3) Use a =E2=80=9Edata= table=E2=80=9C with one column per potential type (fieldid, valstring, val= int, 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 needin= g DDL privileges.

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

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

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

--00000000000032992f0621be8a71--