用于跟踪更改名称的对象的模型
我遇到了一个有趣的问题。我继承了一个守护进程数据库，该进程轮询并接受来自远程嵌入式系统的报告。每个拥有这些系统之一的站点都可以监控十多个不同的油箱。 （实际上，大多数监控 2、3 或 4 个水箱。）
当发生某些情况时，例如水箱被重新填充或水箱达到最低液位，程序会将该事件保存在 Postgres 数据库中。数据库最初构建的方式是将每个油箱的所有信息（燃料类型等）保存在事件记录中，即使有一个单独的“油箱”表。我向表中添加了一个外键字段，以将其与特定的嵌入式单元关联起来，并向事件表中添加了一个外键，以将其与特定的坦克关联起来。
现在的问题是：可以随时添加、删除油箱或更改其存储的燃料类型。添加坦克应该不是问题，但如果删除坦克，记录的那些事件将成为“孤立的”。更糟糕的是，如果燃料类型发生变化，从“喷气式飞机”改为“火箭”，那么当有人搜索历史时，他们会认为所有这些旧事件都发生在“火箭”燃料上，而实际上它们发生在“火箭”燃料上。喷气”燃料。
我离线收到了一些建议：（1）制作第二个坦克存档表，当发生任何变化时，将坦克记录及其唯一 ID 移动到存档表，并使用新 ID 创建新记录对于储罐的新状态，或 (2) 以及储罐表中的“活动”字段，并且在规格更改时仍创建新行，但仅将储罐的当前状态标记为“活动”。
有人对这些提议的解决方案有什么意见，或者有其他可能可行的想法吗？
I have run into an interesting problem. I have inherited a database for daemon process that polls and accepts reports from remote embedded systems. Each site that has one of these systems can monitor over a dozen different fuel tanks. (In practice, most monitor 2, 3 or 4 tanks.)
When something happens, such as a tank being refilled or a tank reaching a minimum level, the program saved that event in a Postgres database. The way the database was originally constructed, it was saving all the information from each fuel tank (type of fuel, etc.) in the event record, even though there was a separate "tanks" table. I added a foreign key field to the table to associate it with a particular embedded unit, and a foreign key to the events table to associate it with a particular tank.
Now here's the problem: tanks can be added, removed, or have the type of fuel they store changed at any time. Adding tanks shouldn't be a problem, but if one is removed, those event recorded would be "orphaned". Worse if the type of fuel is changed, from say "jet" to "rocket", then when someone searches through the history, they would think all those old events happened to the "rocket" fuel, when in fact they happened to the "jet" fuel.
I have received a couple of suggestions offline: (1) make a second, archive table of the tanks, and when anything changes, move that tank record, with its unique ID, to the archive table, and make a new record with new ID for the new state of the tank, or (2) and an "active" field to the tanks table, and still create new rows when specs change, but only flag the current state of the tanks as "active".
Does anyone have any opinion on these proposed solutions, or another idea that might work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只是因为有人在没有完全理解原始设计的情况下添加了外键约束。看看能不能找到那个人，狠狠地跟他谈谈。经常摇动手指。
Only because someone added a foreign key constraint without fully understanding the original design. See if you can find that person, and talk to him sternly. Wag your finger a lot.
问题出在哪里？所有相关信息均在事件记录中；或者当您创建坦克和事件之间的链接时，您没有保留坦克信息？ 。事实上，它是孤立的并不是问题，从事件表中删除燃料类型才是导致问题的原因。
坚持信息转发有几个原因。其中之一就是历史。通过将表链接回坦克实体是很好的，它可以向您显示当前状态。如果“类型”与坦克表上的类型不同，事件表会向您显示历史记录。
我想我对问题的理解不够好：
即使坦克被删除，用户是否也想查看所有事件？
在坦克和具有开始日期和结束日期的坦克类型事件之间添加关联表可以解决问题吗？
将一些坦克信息保留到事件中有什么问题吗？这样您就可以知道事件发生时储罐的状态吗？
Where's the problem? All the relevant information is in the event record; or when you created the link between tank and event you didn't persist the tank information? . The fact that it is orphaned isn't a problem removing the fuel type from the event table is what caused the problem.
There are several reasons why to persist information forward. one of which is history. By linking the tables back to a tank entity is nice it shows you what IS current state. the event table shows you history if the "type" is different than the type on the tank table..
I guess I don't understand the problem well enough:
Are users wanting to see all events even if a tank is deleted?
Would adding an associative table between tank and events for tank type with a start and end dates solve the problem?
What's wrong with persisting some tank information to the event? This way you know the state of the tank at the point in time the event occurred?