Re: Database design: Backwards-compatible field addition

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