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).
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
There’s three ways I can think of that you can go about this, in order of preference:
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.
udfor 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.
You pre-bake in a fixed number of unused columns for each data type in each customizable table. E.g.
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
OriginalTableNameCustomjust 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.
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
FieldValueand 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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0