数据库设计：这是位置记录的一个好办法吗？ [英] Database Design: Is this a good practice for location logging?

问题描述

在下面的图表中，您可以看到我想要做的简化版本。我必须跟踪某些项目的位置，但我也必须有效地检索任何给定项目的最新位置。最简单的方法是查询ItemLocationLog并搜索该项的最新日期，但由于这个表被绑定为非常大，我想知道这是否有效（我猜索引日期时间字段将帮助，但我没有经验来确定多少）。

In the diagram below you can see a simplified version of what I'm trying to do. I have to track the location of certain items, but I also have to efficiently retrieve the latest location of any given item. The simplest way of doing this, would be to query ItemLocationLog and search for the latest date for that item, but since this table is bound to be very large, I'm wondering whether this would be efficient (I guess indexing dateTime field would help, but I don't have the experience to determine just how much).

我想到的另一种方法是在Item上添加一个外键用于日志表如图中带有lastLocation字段所示），它总是指向最新的日志条目，因此它会使我的搜索。另一种选择是在项目上的位置添加外键，并在每次为任何给定项目添加日志条目时更新它。

Another approach I thought about would be to add a foreign key for the log table on Item (as is shown in the diagram with the field "lastLocation"), which would always point to the latest log entry and it would thus spare me the search. Yet another option would be to add a foreign key to Location on Item and update it every time a log entry is added for any given item.

我确信这个是一个简单的解决方案的常见问题，但由于我没有这方面的经验，我怀疑我自己的方法。这种情况的最佳做法是什么？是否可以添加对Item表的引用，以避免昂贵的查询，或者是查询琐碎，我应该从日志表本身获取此信息？

I'm sure that this is a common problem with a simple solution, but since I've had no experience with this, I'm skeptical about my own approaches. What are the best practices for this type of scenarios? Is it ok to add references to the Item table in order to avoid a costly query, or is the query trivial enough that I should just obtain this information from the log table itself?

推荐答案

原则上，如果你有测量的性能，确定实际的瓶颈和结束反正规化实际上会有帮助（足以抵消数据的风险）腐败）。

As a matter of principle, only include redundancies in your model if you have measured the performance, determined the actual bottleneck and concluded the denormalization would actually help (enough to offset the risk of data corruption).

这不会在你的情况下，奇怪的是。 B树索引如何工作的一个特点是搜索MAX基本上与搜索精确值一样快。

Which it won't in your case, curiously enough. One peculiarity of how B-Tree indexes work is that searching for MAX is essentially as fast as searching for exact value. You might have a little bit of a boost from better caching if INT is smaller than DATETIME on your DBMS, but not much.

索引是非常强大，如果做得对。并且索引 ItemLocationLog {idItem，dateTime} 应该方便快速地 SELECT MAX（dateTime）FROM ItemLocationLog WHERE idItem =？。

Indexing is very powerful, if done right. And index on ItemLocationLog {idItem, dateTime}should facilitate lightning-fast SELECT MAX(dateTime) FROM ItemLocationLog WHERE idItem = ?.

查看使用The Index，Luke！来了解有关该主题的精彩介绍。

Take a look at Use The Index, Luke! for a nice introduction on the topic.

这篇关于数据库设计：这是位置记录的一个好办法吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！