Re: Database design: Backwards-compatible field addition

Thanks for you reply.

On Wed, Jun 18, 2008 at 9:15 PM, Shane Ambler <pgsql(at)sheeky(dot)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.