CODEWITHSUNDEEP
sqlschemaversioningobsolete
James Fung
James Fung

Reputation:

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

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

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

Upvotes: 2

Views: 308

Answers (3)

JoshBerke
JoshBerke

Reputation: 67068

I would consider using a name-value approach for all the extended data. Essentially you define your static data from year over year. This will be things like Company information, the definition of an Address for example doesn't change year after year. These will be modled normally.

Then you would define a table that will contain a master of all the questions you have, and will be linked somehow to tell you what year those questions are valid for. This table might also indicate other attributes about the question that could let you dynamically create a GUI on top of it. Things such as regular expressions to validate the type of data etc.

Here's a really naive approach which even after doing this would not be the end state of what I would model (I would probally have another table the correlates a year to a question, and this is what I would link the company too. this way we can reuse questions over and over).

Note: I have a picture to go along with tihs need to find a place to upload it too...Will edit soon as I get that up

Upvotes: 1

JeeBee
JeeBee

Reputation: 17546

"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.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 5

Related Questions