Hi list. If you have an existing table, and apps which use it, then how do you add new fields to the table (for new apps), but which might affect existing apps negatively? eg: I start with a table like this: table1 - id - field1 - field2 - field3 Later, I want to add a use case, where there is new behaviour, if a new field is set in the table, like this: table1 - id - field1 - field2 - field3 - field4 - NEW - if unset, do old behaviour. if set, do something else The problem is, that existing apps (besides your new app) won't know about field4, so they will keep using the old behaviour for new records (where field4 is set), which you don't want. The most obvious thing to do is to update all apps using table1, so they also check the value of field4. Is there another, more backwards-compatible way to add field4 for the new behaviour, without having to update all the apps? A few things I can think of: 1) table1 becomes a view of an updated table, with a 'WHERE field4 IS NULL' clause. Problem with this is that some RDBMS (Postgresql specifically) don't let you run update statements on views. 2) Apps use stored procedures for all database access. Maybe ok for new apps, not so much for existing apps which use regular SQL. 3) All apps use the same library for accessing database Then you update the library and all apps automagically know about the extra field. Again, maybe ok for new apps, not so much for existing apps. 4) Make a new table (copy of the old one), with the extra field. Then your app checks both tables, or just the new one if applicable. This can work, but you may end up with a lot of app-specific tables, where the main difference between the tables is extra columns, and which apps use the tables. 5) Have a 'db version' column in the table. Older apps only operate on records at or before the version the programmer knew about at the time. This can work, but it seems like a very non-standard, hackish way of designing database tables. Also it's a pain for all apps to have to hardcode a db version number. 6) Find a clever way to use table inheritance I haven't thought it through, but here are some docs I've read on the subject: http://www.postgresql.org/docs/8.1/static/ddl-inherit.html Any other ideas? David.
