Creating new table for new fields

Question

I have never made a database with the ability for an administrator (meaning, a admin client using the front end interface) to create custom fields for an entry.

Thinking this through, I think it might make sense to create a table called custom_fields with information about any new fields that will be created (like date created, field name, field type etc).

Then, create a new table for that field and link new records to it's corresponding record on the original table.

I think it makes sense because I won't be altering the original records, rather just adding to new ones.

Does this make sense? I haven't found anyone online that suggests this (as far as I saw).

Answer 1

There's three ways I can think of that you can go about this, in order of preference:

1. You have your application run the appropriate SQL scripts to alter the corresponding tables to add the new fields to. Typically one uses a prefix or suffix (e.g. ud for user defined) as a quick way to identify what fields are custom. I'm not opposed to having a separate meta-data table about those custom columns too, as you mentioned. This is fairly common practice among enterprise level software such as ERP systems.

2. You pre-bake in a fixed number of unused columns for each data type in each customizable table. E.g. IntColumn1 through IntColumn10, DateTimeColumn1 through DateTimeColumn10, VarCharColumn1 through VarCharColumn10, etc. Then your application allows the end user to "add" a new field by having them select the data type and the application chooses the next unused column of that type to map their new field to. I would probably put all of these additional fields into separate tables called OriginalTableNameExtension or OriginalTableNameCustom just so I wouldn't have to look at them and can manage them separately from the main tables. I've also seen this solution in the wild used by enterprise software.

3. The Entity-Attribute-Value Model, which is a single generic table that stores data of all different types. Similar to your meta-data table, typically it would have columns such as TableName, FieldName, FieldType, FieldValue and would store 1 row per value per field per table, in your case, all the custom fields. This allows the ability for custom fields to be created on the fly without any schema changes to the database, and is really flexible from a developer perspective. But it's unfortunately an anti-pattern from a database perspective. This is because the architectural structure can affect performance and makes it harder to index appropriately. It also causes queries to be written in less than usual ways to recompile the data needed to fit a specific use case for display reasons, especially when you start getting into analytical queries against the data.