Re: Database design: Backwards-compatible field addition

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