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?