Creating tables dynamically at runtime

Question

Is creating a table dynamically at runtime based on a user's interactions a good or bad thing for a web-app? (I'm talking java, but question probably applies to more).

Answer 1

Tables are generally crucial to a user's interaction with the database. Consequently the absence of tables is fatal.

From this it follows, that creating tables on the fly, at runtime, is a bad practice because it means there is no guarantee of the user's experience. If a CREATE TABLE statement fails, for whatever reason, the user is stuffed.

So, it is q good idea to avoid business processes which rely on the runtime creation of tables. There are usually workarounds, except in very specific circumstances.

To a certain extent this depends on the flavour of RDBMS underpinning the application. For instance, Oracle has the concept of Global Temporary Tables which removes the call for dynamic table creation in almost all circumstances. But even without such fancy features, there are usually ways around it: for instance, adding A USERNAME column to a table and building a view on top of it which includes a WHERE clause filtering on USERNAME=USER.

Basically, DDL is expensive to execute, in terms of elapsed time and system resource. It creates transactional complexity. And it is risky: if it fails then the user cannot proceed. So for all these reasons it should be avoided.