How should I implement custom database fields?

Question

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?

Answer 1

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.

Answer 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.

Answer 3

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.