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-pg...@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 <matth...@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. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
