DB设计：配置数据、实际数据、日志数据
我想知道是否有任何典型的方法来区分这种数据
，我必须在数据库中列出设备（例如），每个人都会有
- 配置数据
- 实际数据
- 日志数据
我通常将配置/实际数据混合在同一个表和另一个表用于记录数据，
这似乎是一个常见问题，所以我想知道是否有任何标准方法可以做到这一点。
编辑：：
这是一个示例
街道上的信号量：
- 配置数据 =位置（街道交叉口），类型（对于乘客， 汽车）等..
- 实际数据 = colorstate=红色、绿色、步行、停止..功能=正常、正在维修等..
- 日志数据 = 日期/时间+ colorstate +（需要记录的任何其他实际数据）
谢谢
I want to know if there is any typical approach to differenciate this kind of data
I have to listing devices (for example) in a db, ane everyone will have
- Configuration data
- Actual data
- Log data
I commonly mix Config/Actual Data in the same table and another table for Log data,
This seems to be an usual issue, so I wonder if there is any standar way to do it.
EDIT::
Here an Example
A semaphore at the street :
- Config data = position (street intersection), type (for passengers,
cars), etc.. - Actual data = colorstate=red, green, walk, stop.. functionality=ok, being repaired, etc..
- Log data = date/time + colorstate + (any other of the Actual data needed to be logged)
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你可以像这样划分它（对sql语法和类型懒惰）：
`
signal_config
id (key)
位置
类型
signal_log
signal_id、时间戳（复合键）
color_stat 之一（红、黄、绿）
功能状态
在我
看来，信号中有些东西不会改变，比如它的位置和类型，而有些东西确实会改变，比如它的修复状态和颜色。通过此表，您可以查询特定一天中灯既亮又红的时间。
You might divide it up like so (being lazy about sql syntax and types):
`
signal_config
id (key)
position
type
signal_log
signal_id, timestamp (compound key)
color_stat one of (red, yellow, green)
functioning_state
`
To my mind, there's stuff that doesn't change about the signal, like it's location and type, and stuff that does change like its repair state and color. With this table you could query for the times on a particular day that the light was both working and red.
我认为这里的术语有点混乱。
您所说的“配置数据”对我来说听起来像“业务密钥”或“候选密钥”：一组（可能）不变且唯一标识实体的数据：在数据末尾只有一个红绿灯主街。
您所说的“实际数据”似乎是实体的属性，会随着时间的推移而变化。
所以我认为将它们放在同一张表中不存在设计问题。
在单独的表中记录“日志数据”（审计跟踪、历史记录）也是一个非常好的主意。
I think there's a bit of confusion over terminology here.
What you're calling "config data" sounds to me like "business key" or "candidate key": a set of data which is (probably) unchanging and which uniquely identifies the entity: there is only one traffic light at the end of Main Street.
What you're calling "actual data" seems like the attributes of an entity, which change over time.
So I don't think there is a design problem putting them in the same table.
Also it is a very good idea to record "log data" (audit trail, history) in a separate table.
看起来您会受益于正确地将这些信息规范化到多个表中。
看起来您不应该考虑将所有内容都塞到一张表中。
it looks like you would benefit from properly normalizing this information into several tables.
it does not look like you should consider jamming it all into one table.
您的配置和实际应该进入不同的表。配置似乎相当静态，并且当写入很少写入的表时，不会随着时间的推移而产生碎片并降低性能。实际的数据表会更小，然后可以使用索引、分区、填充索引等针对与配置的关系进行优化。实际的表可能会更频繁地产生碎片，但应该能够快速重建，因为它包含的数据较少。
以有利于您流行的搜索方法的方式优化日志表。如果您在大多数情况下按日期范围搜索，则会按记录日期对表进行聚类。如果通过配置项的 id 进行搜索，请考虑按 id 进行分区或集群，并允许表分段，并根据配置项的 id 使其分段。
Your config and actual should go into different tables. The config seems to be rather static and when written to a table experiencing very few writes will not fragment and degrade performance over time. The actual data table would be smaller and could then be optimized for the relation back to the config using indexes, partitioning, pad index, etc. The actual table may fragment more often but should be able to be rebuilt quickly because it contains less data.
Optimize the log table in a way which benefits your prevelant search method. If you search by date range on most occasions cluster the table by logged date. If the search is by an id of a config item consider partitioning or just cluster by the id and permit the table to fragment, and leave it fragmented based on the id of the config item.