Re: [GENEAL] dynamically changing table

On Mar 30, 2009, at 5:39 PM, A B wrote:

> Hi,
> In the next project I'm going to have a number of colums in my tables,
> but I don't know how many, they change. They all use integers as
> datatype though.. One day, I get 2 new columns, a week later I loose
> one column, and so on in a random pattern.

Ignoring design implications (you got enough replies about that I
think)...

You could add the columns you're sure that you need and put the rest
in an XML field. That field can then contain any custom fields that
you don't need right away while you still have the data available in a
useful fashion. If it turns out some of those custom fields should end
up in the table as a column it isn't hard to extract the data from the
XML field.
It's probably a good idea to add a version attribute or field to your
XML that you increment on each model change (an xsd would be even
better, it allows you to specify types for your data) so that you know
which fields to expect in the document.

You can query XML fields using xpath expressions in your queries.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,49d20a0c129741113880388!