pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase schema for "custom fields"
5+ messages / 5 participants
[nested] [flat]
* Database schema for "custom fields"
@ 2024-09-10 06:57 Matthias Leisi <matthias@leisi.net>
0 siblings, 2 replies; 5+ messages in thread
From: Matthias Leisi @ 2024-09-10 06:57 UTC (permalink / raw)
To: pgsql-general
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@leisi.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Database schema for "custom fields"
@ 2024-09-10 07:08 Muhammad Usman Khan <usman.k@bitnine.net>
parent: Matthias Leisi <matthias@leisi.net>
1 sibling, 1 reply; 5+ messages in thread
From: Muhammad Usman Khan @ 2024-09-10 07:08 UTC (permalink / raw)
To: Matthias Leisi <matthias@leisi.net>; +Cc: pgsql-general
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@leisi.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@leisi.net
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Database schema for "custom fields"
@ 2024-09-10 09:11 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Muhammad Usman Khan <usman.k@bitnine.net>
0 siblings, 1 reply; 5+ messages in thread
From: Peter J. Holzer @ 2024-09-10 09:11 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2024-09-10 12:08:14 +0500, Muhammad Usman Khan wrote:
> 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.
I think this is essentially Matthias' option 3:
> On Tue, 10 Sept 2024 at 11:57, Matthias Leisi <matthias@leisi.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).
[...]
> How would you design this from a DB point of view? I see a few options, but
> all have some drawbacks:
[...]
> 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?
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Database schema for "custom fields"
@ 2024-09-10 12:08 Shaheed Haque <shaheedhaque@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 5+ messages in thread
From: Shaheed Haque @ 2024-09-10 12:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
The relational purists will gave their concerns, but especially given what
you described about your performance and volumetrics, there is a reason why
JSON(b) is a thing. For type checking, and more, I've had success a
multi-key approach so that one entry might comprise:
- A "name"
- A "type"
- A "value"
Of course you can add more as needed.
On Tue, 10 Sep 2024, 10:11 Peter J. Holzer, <hjp-pgsql@hjp.at> wrote:
> On 2024-09-10 12:08:14 +0500, Muhammad Usman Khan wrote:
> > 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.
>
> I think this is essentially Matthias' option 3:
>
> > On Tue, 10 Sept 2024 at 11:57, Matthias Leisi <matthias@leisi.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).
> [...]
> > How would you design this from a DB point of view? I see a few
> options, but
> > all have some drawbacks:
> [...]
> > 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?
>
> 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
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp@hjp.at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Database schema for "custom fields"
@ 2024-09-11 13:24 Greg Sabino Mullane <htamfids@gmail.com>
parent: Matthias Leisi <matthias@leisi.net>
1 sibling, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2024-09-11 13:24 UTC (permalink / raw)
To: Matthias Leisi <matthias@leisi.net>; +Cc: pgsql-general
I'd go with option 2 (jsonb), as it's likely already well supported by your
applications, while the other approaches will require a good bit of
customization. JSONB can be indexed, so performance should be on par with
"traditional" tables.
Cheers,
Greg
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-11 13:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-10 06:57 Database schema for "custom fields" Matthias Leisi <matthias@leisi.net>
2024-09-10 07:08 ` Muhammad Usman Khan <usman.k@bitnine.net>
2024-09-10 09:11 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2024-09-10 12:08 ` Shaheed Haque <shaheedhaque@gmail.com>
2024-09-11 13:24 ` Greg Sabino Mullane <htamfids@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox