2

I'm building an app where end users can add custom fields to database tables. Think of it like a CRM app, where sometime you just need to add an extra field to your customer table. Pretty much all CRMs have this feature. I'm using a traditional SQL database.

Should I do it by changing the data structure itself via ALTER TABLE ADD column? Or should I store the contents of the new field as a key/value pair in a separate table, i.e. (id, fieldname, fieldvalue)?

The advantage of ALTER TABLE is that it's simpler. The advantage of the separate table is that letting end users ALTER TABLE gives me the willies.

The big disadvantage of the separate table is that it greatly complicates queries because you've got to use some ugly joins everywhere.

Anyway, how do most apps do it?

  • Each end user can add custom fields to some tables, affecting any records in the table, visible for any other user of the system, in a system-wide manner? Or does each user have his one database? Or do you mean that a specific installation of your system can be customized by an admin or power user for all other users? Or can each user modify the table structure for a restricted a subset of the data (like a "project"), so the modifications are only available for the users which can access the project? Please clarify which model you have in mind! – Doc Brown Aug 29 '18 at 5:58
  • This seems like it could be a duplicate: softwareengineering.stackexchange.com/questions/148551/… – Andy Aug 29 '18 at 22:11
  • @DocBrown Would work like most SaaS CRMs, where an admin adds the fields, and the fields are visible to users on that account, but not to users on other accounts. I'm thinking this means that every account gets its own database, but that introduces its own issues. – ccleve Aug 31 '18 at 22:36
5

You could go a little mongo-ish, and store custom fields as JSON in a text field. Later versions of MySQL and Postgres can even query on these.

This also means those fields are only on the rows that need them, rather than having lots of actual db fields full of null.

You may need some sort of data dictionary - perhaps in a table, so you know what to do with each custom field.

2

The two ways I've seen it done is either key/value as you mentioned, or sometimes the developer will just add a fixed number of fields Custom1, Custom2, ... Custom10 to the database. You just tell the customer they have a limit of 10 fields that they can use as they wish.

ALTER TABLE won't scale. You'll wind up with hundreds of user defined columns that will be mostly NULL, and you'll have problems when your table is big and someone decides to add a column.

  • I envision some middle-ground solution where the user is allowed to add a limited number of first-class fields with real names and types. – Robert Harvey Aug 28 '18 at 23:33
  • 1
    In addition, ALTER TABLE could be a problem if later you'd opt for multi-tenancy, for example if your system is supposed to be used as a SaaS in the cloud for more than one customer. – Christophe Aug 29 '18 at 12:45
2

Options as I see them:-

(First couple already suggested by Dan Pichelman)

1) Custom1..CustomNN with a table (containing NN rows) to assign meaningful names and validation to the custom fields.

2) Key-Value pairs which would need a table to define the additional fields and another table to link the primary key with this table.

Both of these approaches will end up with data type issues e.g. integers stored in VARCHARs.

3) Plug-in architecture - like open source CRMs you design the architecture around plug-ins that will create their schema changes on installation and provide the tools to manipulate them. Which needs technically minded users.

4) Consultancy Approach A - You let people pay you to add new fields which are then shared with the other users. Advantage - still only one codebase to support, Disadvantage - Can't charge as much because no exclusivity, also, customers may wait for someone else to cave in and pay so they get it free. There are some ways to get around this.

5) Consultancy Approach B - You customise the product for every user willing to pay you and do not share the new fields with other customers (unless they pay as well). Advantage - High hourly billing rate, can charge many users for the same change. Disadvantage - Codebase fragmentation, managing who's paid for what. Partially alleviated by selling temporary exclusivity instead of permanent exclusivity i.e. exclusively yours until the next major release.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.