每年需要更改数据库架构。 应该使用哪种策略？
我们公司每年都会举办一次会议/展位，参与者可以展示他们的产品。
我们有一个网络应用程序，可以让参与者注册参加会议。 他们可以输入公司名称、账单信息等信息。
似乎每年对参与者需要输入哪些信息的要求都不同。
即，一年参与者可能需要输入他们想要的展位尺寸，第二年就不再需要，依此类推。 今年，您可能只需要输入所需的 m^2 总数，而下一年，您可能需要添加所需的长度、高度和楼层数。
多年来，这导致数据库模式变得相当疯狂。 现在我们的数据库中有很多“过时”的字段和表，并且它开始看起来相当混乱。 由于历史原因，我们不能只是将每年的模式重置回基础。 我们可能需要一些旧会议的数据。
那么：有人对我们如何处理这个问题有好主意吗？ 我能想到的唯一解决方案是
- 为每个会议版本我们的数据库，即将
- 所有“变化”信息存储为 xml
如果有人有一些关于如何处理不断发展的数据库和处理过时数据的好文献，那就太好了！
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 !
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尽管我不想这么说，但这可能是实体-属性-值结构最有效的情况。
http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
请注意，这不是一个可以轻易使用的模型，它存在重大问题。 但这正是它旨在解决的问题。
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.
我会考虑对所有扩展数据使用名称-值方法。 本质上，您可以逐年定义静态数据。 这将是诸如公司信息之类的信息，例如地址的定义不会年复一年地改变。 这些将被正常建模。
然后，您将定义一个表，其中包含您拥有的所有问题的主表，并将以某种方式链接起来，告诉您这些问题的有效年份。 该表还可能指示有关该问题的其他属性，这些属性可以让您在其之上动态创建 GUI。 例如正则表达式来验证数据类型等。
这是一种非常幼稚的方法，即使这样做之后也不会是我要建模的最终状态（我可能会有另一个表将一年与一个问题相关联，并且这也是我将公司联系起来的方式，这样我们就可以一遍又一遍地重复使用问题）。
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).
“现在我们的数据库中有很多‘过时’的字段和表，并且开始看起来相当混乱。由于历史原因，我们不能只是将每年的模式重置回基础。我们可能需要一些来自旧会议的数据。”
如果您可能需要它们，它们并没有过时。
不过，我会对前端进行一般性的编码。 这意味着拥有一个可以处理任何形式的展台区域配置（在您给出的示例中）的系统，并且如果将来发生这种情况，可能会处理更多。
如果您有诸如“standarea”（面积以 m^2 为单位）、“standsize”（长度、宽度、高度等）之类的表格 - 那么您的模型中将有对象来匹配这些（StandArea、StandSize） - 这些都可以扩展一个公共基类StandData。
一年一个表获取数据集，第二年另一个表获取数据。 您的 DAO 将尝试从每个表加载每个对象（通过 Parent、err、stand_uid 字段），然后将“ConferenceApplication”对象中的 StandData 字段设置为它发现的任何内容。
另一种选择是将所有可能的字段放在一个表中，并允许它们为空。
"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.