The company I'm currently working for has a feature in an enterprise app where a user can add columns to a table configuration on a web GUI through fields and a button, and a corresponding SQL table has columns added (or altered or removed). That is, the table is shaped in run time by the user's preferences.
This feels wrong to me, but I'm struggling to clarify to myself and to others what the limitations are of this architecture vs one that uses a 'design time' style - for example, having the user's input added as a row to a 'Columns' table, which includes a foreign key to the 'Tables' table.
This 'run time' approach doesn't appear to violate the forms of normalization as far as I understand them. There'll be a performance hit each time a table needs to be updated, but this is a niche business app with maybe a few dozen active users at a time in an organisation, so it doesn't need to scale.
There are development/testing issues with this because adding test data means making corresponding database changes. But since testing is all manual at this company, and test databases are just copied wholesale between versions, it doesn't seem to have as big an impact as one might think.
The GUI has a number of validation checks to ensure columns are added safely. I also feel uncomfortable that the front end (AngularJS) writes directly to the database, instead of going through a back end API, but again the application is niche enough that the company can ensure users always go through the GUI. And it's installed on private corporate networks, so in theory it's not exposed to public attacks on the database directly.
The company is very invested in the current architecture so is unlikely to change this app, but I'd like to at least build awareness about the consequences of this approach so future apps can be designed in a better way.
I'd like to understand the following:
- What are the performance differences between a user adding a column dynamically, vs adding a row in an existing table?
- What are the security vulnerabilities that this approach creates, if any?
- Are there any industry standards, guidelines or best practices I can reference as supporting one or other database design?