Database schema changes needed yearly. Which Strategy should be used?

Question

Hello,

Every year our company holds a conference/stand where participants can show their products.

We have a web-application which let the participants sign up for the conference. They can enter information such as the name of their company, billing information, and so on.

It seems as if the requirements for what information the participants need to enter, vary from year to year.

I.E , one year the participants might need to enter the size of the stand they want, the next year this is no longer needed, and so on. One year, you might just have to enter a total number of m^2 you want, while the next year, you might need to add the length, height and number of floors you want.

Over they years, this has caused the DB schema to become quite crazy. We now have a lot of 'obsolete' fields and tables in our database, and it's beginning to look quite messy. For historical reasons, we can't just reset the schema back to basics for each year. We might need some of the data from the old conferences.

So: Does anyone have a good idea on how we can deal with this ? The only solutions I can think of are

• Version our database for each conference i.e
• Store all of the 'varying' information as xml

If anyone has some good litterature for how to handle evolving databases and dealing with obsolete data, it would be good !

Answer 1

much as I hate to say this, this might be case where the Entity-attribute-value structure would work best.

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

Note this is not a model to use lightly, there are significant problems with it. But this iexactly the kind of problem it is designed to solve.

Answer 2

"We now have a lot of 'obsolete' fields and tables in our database, and it's beginning to look quite messy. For historical reasons, we can't just reset the schema back to basics for each year. We might need some of the data from the old conferences."

If you might need them, they're not obsolete.

I would code the front-end generically however. This means having a system that can handle any form of stand area configuration (in the example you give), and maybe more in the future if that should occur.

If you have tables like "standarea" (area in m^2), "standsize" (length, width, height, etc) - then you would have objects in your model to match these (StandArea, StandSize) - these could both extend a common base class StandData.

One year one table gets data set, the next year another table gets the data. Your DAO will try to load each object from each table (by a parent, err, stand_uid field) and then set the StandData field in your "ConferenceApplication" object to whatever it discovered.

The other option is to just have all possible fields in a single table, and allow them to be empty.