What are the pros and cons of adding SQL columns in run time, vs through a different design time architecture?

Question

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:

1. What are the performance differences between a user adding a column dynamically, vs adding a row in an existing table?
2. What are the security vulnerabilities that this approach creates, if any?
3. Are there any industry standards, guidelines or best practices I can reference as supporting one or other database design?

Answer 1

The ability for a customer to add arbitrary fields is pretty common for certain kinds of internal enterprise applications. There are several options to implement functionality like this, your example is probably the more unusual option. A typical implementation is the Entity Attribute Value model, which is probably what you mean by addding rows instead of columns. This doesn't require changing the DB schema at all, but it does make queries more complex and slower.

In many modern databases an alternative to EAV is to add a JSON column. You can add arbitrary keys to that column without changing the schema. Performance is generally slower than a fixed DB schema, but for many kinds of queries still pretty good. I'm most familiar with Postgres in this area, and there are plenty of tools to query and index JSON there.

Allowing the customer to change the schema is an unusual option. In terms of security, one impact is certainly that the DB user must have permissions to alter the schema, which isn't necessary with the alternatives. You're also writing that the frontend is writing directly to the DB, I'm seriously hoping you are misunderstanding the architecture here. If the frontend can actually execute arbitrary SQL on the DB, that is a much, much bigger issue than this design. If the frontend can execute arbitrary SQL, your application is broken, there is no data integrity and there isn't any security. Any user can circumvent any permissions system you have, change or delete any data or just break your database entirely.

One issue with this design is that your users are probably not database experts, and might not understand all the effects certain choices have. This would be especially noticeable if your database is very large. Database operations that change the schema generally have to acquire very restrictive locks on tables, so while they are changing the schema they might block anyone else from writing or even reading the table. In some databases e.g. adding a non-null column with a default value has to rewrite the entire table, which can take a long time and also block other access to that table. These kinds of issues tend to go unnoticed in low-traffic and low-data environments, but at larger scale they can easily bring down your application.