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