Application that modifies its underlying db schema

Question

Under what circumstances (if any) would you allow an application to change its underlying database schema? I.e. to add new column.

EDIT: A little bit of elaboration on what prompted me to ash this question. My system receives raport files from other systems and saves fetched data in "reports" table. Report includes several "parameters" which are now hard-coded: date, amount, unit_price. This parameters are saved in adequate columns in "reports" table. We need to introduce the possibility of dynamically (via web interface) adding new parameters which can be read from report file and inserted to db.

One option is to save reports in some serialized form but there's performance issue. There will be thousands of reports each day and there will be a need to query "reports" table by parameters' values.

Answer 1

To put it simply, never. Changing a database structure should be a controlled change - allowing an application (other than, for example, an application designed to upgrade a database from one version to another) is breaking this control.

Perhaps you can elaborate more on why you think this is a good idea, or what's prompted you to ask the question.

Answer 2

I wouldn't - ever. I know it is possible to make changes to POJOs and allow Hibernate, for example, to propogate any changes necessary to the database, but this is IMHO a bad idea, as it removes the ability to exercise proper configuration management of your schema.

EDIT: possible exception: see Andy's answer (although I would still try and separate an update step from the application itself).

Answer 3

On first load of a new version. This is especailly relevant for systems which assign a database to each unique instance. Data writes should be marked with a system variable, and tested before update with something like

show columns from test like 'new_column';
if ( ! new_column )
  alter table add column new_column after some_column

With regards to the other posts, if you are running a system with incremental versioning on the same database schemas (but different instances of those schemas) the only way to manage that change is to programmatically update the database - otherwise you'll have inconsistencies between the application versions (i.e. 1.0 and 1.1) and the schemas - it's not possible to apply the database changes across all databases at the same time in this instance.