Обсуждение: Database design: Backwards-compatible field addition
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.
On Wed, Jun 18, 2008 at 02:04:14PM +0200, David wrote: > 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. Given 1) the view will be "fairly uncomplicated" and hence "fairly straightforward" ON INSERT/UPDATE/DELETE rule can likely be added to it allowing for an apparently writable view. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
David wrote: > 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? > If you know you are going to add a column then add it now and just not have your app do anything with any data there. Old apps don't know the new field4 exist and work as they did before it was added. New apps work with data in field4 and apply default behaviour when it is null. The way that old apps get caught up in this scenario is when you use SELECT * ... then loop through the index of columns returned. The safe design for your apps is to use SELECT field1,field2,field3 FROM... giving your app a rigid data structure to work with, then either loop through the column indexes or use column names to access the data returned. Of course dropping a column breaks old apps and you need to weigh up these changes to decide whether app v1.x will continue to be usable or must be upgraded for further use. Of course you can leave an old column there for old apps and have new apps ignore it eg. remove it from SELECT statements in the new app. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
>> Problem with this is that some RDBMS (Postgresql specifically) don't >> let you run update statements on views. > > Given 1) the view will be "fairly uncomplicated" and hence > "fairly straightforward" ON INSERT/UPDATE/DELETE rule can > likely be added to it allowing for an apparently writable > view. > Thanks for the info. I had the mistaken idea that rules and triggers were only for real tables & not views (I've never used them before). I would like to avoid using them if possible (extra complexity, and don't want to rename tables & make new views each time I need to make backwards-incompatible app updates), but they are there if I need them. David.
Thanks for you reply. On Wed, Jun 18, 2008 at 9:15 PM, Shane Ambler <pgsql@sheeky.biz> wrote: > David wrote: >> >> 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? >> > > If you know you are going to add a column then add it now and just not have > your app do anything with any data there. > I don't have a problem with this case. The problem is when older apps need to do something different (usually ignore) records which have certain values (usually anything non-NULL) in the new fields. Simple (toy) example. You have a table like this: employee - id - name - ...etc.. You have a lot of software which uses this table. Later, you need to add an 'employed' boolean field, to reflect whether an employee is still working at the company Your new apps know the difference between employed and unemployed employee, but old apps all assume that all employees in the table are currently employed, and will want to send them pay checks, emails, etc. Furthermore, assume that this kind of change happens fairly often. Would you make more views & rules each time the requirements change? Would you need to update all the apps each time too? Or are there other methods (version columns, etc) which can reduce the work required in cases like this? David.
David wrote: > Later, you need to add an 'employed' boolean field, to reflect whether > an employee is still working at the company > > Your new apps know the difference between employed and unemployed > employee, but old apps all assume that all employees in the table are > currently employed, and will want to send them pay checks, emails, > etc. > > Furthermore, assume that this kind of change happens fairly often. > > Would you make more views & rules each time the requirements change? > > Would you need to update all the apps each time too? > > Or are there other methods (version columns, etc) which can reduce the > work required in cases like this? Well a couple of ways to tackle a change like this. One is to move old employees into an employees archive table. Old apps won't see the new table and old employee records. New apps can union the two tables to get what they want. Another way is to rename the employees table and replace it with a view called employees which is defined with a WHERE employed = true. Leaving it based on one table will make adding rules simple so that old apps can insert to the employees table (which is now a view) and not know any different. New apps can select from the new table name if they want historic data. It really depends on the change you need to make and how the simple way to make the change will affect existing apps. You need to make these choices as a change is needed to your app. Of course having some insight as to what areas are likely to have changes can help you design now to make it easier later. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz