I would like to ask for your opinion about a question even though I already got mine so that I can see if my thinking is coherent or not. I won't expose my opinion at first in order not to influence anyone (in a way or another).
我想问你对一个问题的看法，尽管我已经有了我的想法，这样我就能看出我的想法是否连贯。为了不影响任何人(以某种方式)，我一开始不会公开我的观点。
Basically I have a table for storing some kind of object data. Those objects have a status that can change and I need to keep track of those status changes. Now I would like to know how you would design the rest of the database to do so.
基本上，我有一个用来存储某种对象数据的表。这些对象具有可以更改的状态，我需要跟踪这些状态更改。现在我想知道如何设计数据库的其余部分。
In addition I can tell you that a status change is made at a certain time that must be remembered as well. And finally you must be able to determine what the current status is at any time. The question is actually more related to how you would store and read the current status.
此外，我还可以告诉您，状态更改是在必须记住的特定时间进行的。最后，您必须能够在任何时候确定当前状态。这个问题实际上与你如何存储和读取当前状态有关。
Please do not hesitate to ask me if you need more info to answer. I just don't want to give too much in order not to influence the answers.
如果你需要更多的信息来回答，请尽管问我。我只是不想为了不影响答案而付出太多。
PS: I do not need trigger related answers
我不需要触发器相关的答案
1 个解决方案
#1
2
Basically you want a status history table separate from the object table.
基本上，您希望状态历史表与对象表分开。
objects [id, name, ...]
status_history [object_id, status, valid_from, valid_to]
Optionally you can include current status and timestamps in objects table if you need it - it will certainly speed up queries when you need to know just the current status (which is often most of them) for the cost of maintenance.
如果需要的话，还可以在对象表中包含当前状态和时间戳——当您需要知道当前状态(通常是大多数状态)的维护成本时，它肯定会加速查询。
If you want to historize more attributes than just status or the entire record you could stay with a single table:
如果你想要更多的属性，而不仅仅是状态或者整个记录，你可以留下一张桌子:
objects [id, seq_number, name, ...., status, valid_from, valid_to]
#1
2
Basically you want a status history table separate from the object table.
基本上，您希望状态历史表与对象表分开。
objects [id, name, ...]
status_history [object_id, status, valid_from, valid_to]
Optionally you can include current status and timestamps in objects table if you need it - it will certainly speed up queries when you need to know just the current status (which is often most of them) for the cost of maintenance.
如果需要的话，还可以在对象表中包含当前状态和时间戳——当您需要知道当前状态(通常是大多数状态)的维护成本时，它肯定会加速查询。
If you want to historize more attributes than just status or the entire record you could stay with a single table:
如果你想要更多的属性，而不仅仅是状态或者整个记录，你可以留下一张桌子:
objects [id, seq_number, name, ...., status, valid_from, valid_to]