数据库设计：相同的表结构但不同的表
我的最新项目处理大量“暂存”数据。 就像当客户注册时，数据存储在“customer_temp”表中，当他通过验证时，数据被移动到“customer”表中。
在我开始发送电子邮件之前，请大肆宣扬我认为这是错误的，你应该在行上放一个标志，总有可能我是白痴。 有人可以向我解释为什么这是可取的吗？ 创建 2 个具有相同结构的表，填充一个表（表 1），然后在发生某些事件时将整行移动到另一个表（表 2）。
我可以理解表 2 是否会存储存档的、不很少使用的数据。
但我不明白表 2 存储的实时数据是否会不断变化。
回顾一下： 谁能解释一下这种看似适得其反的方法是多么错误（或正确）？
My latest project deals with a lot of "staging" data.
Like when a customer registers, the data is stored in "customer_temp" table, and when he is verified, the data is moved to "customer" table.
Before I start shooting e-mails, go on a rampage on how I think this is wrong and you should just put a flag on the row, there is always a chance that I'm the idiot.
Can anybody explain to me why this is desirable?
Creating 2 tables with the same structure, populating a table (table 1), then moving the whole row to a different table (table 2) when certain events occur.
I can understand if table 2 will store archival, non seldom used data.
But I can't understand if table 2 stores live data that can changes constantly.
To recap:
Can anyone explain how wrong (or right) this seemingly counter-productive approach is?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果业务逻辑中的“客户”和“潜在客户”之间存在显着差异，则在数据库中将它们分开是有意义的（例如，您不需要总是记住通过标志进行查询）。特别是如果两者存储的数据将来可能会出现分歧。
它使报告变得更加容易，并减少了将两种类型的实体视为同一实体的机会。
然而，正如您所说，这看起来确实多余，并且可能不是大多数人设计数据库的方式。
If there is a significant difference between a "customer" and a "potential customer" in the business logic, separating them out in the database can make sense (you don't need to always remember to query by the flag, for example). In particular if the data stored for the two may diverge in the future.
It makes reporting somewhat easier and reduces the chances of treating both types of entities as the same one.
As you say, however, this does look redundant and would probably not be the way most people design the database.
关于为什么你想要“customer_temp”似乎有几种解释。
正如您所指出的，将用于存档目的。为了允许分析数据，但在这种情况下，应该根据一些有趣的查询来聚合历史数据。然而，它使用实时数据听起来不太合理
正如所指出的，可能存在某种区分客户和潜在客户的业务逻辑。
或者它可能是一项安全功能，除了存储已批准的客户之外，还需要记录所有注册客户的尝试。
There seems to be several explanations about why would you want "customer_temp".
As you noted would be for archival purposes. To allow analyzing data but in that case the historical data should be aggregated according to some interesting query. However it using live data does not sound plausible
As oded noted, there could be a certain business logic that differentiates between customer and potential customer.
Or it could be a security feature which requires logging all attempts to register a customer in addition to storing approved customers.
每当我看到永久表名称“customer_temp”时，我都会看到一个危险信号。这通常意味着某人正在解决问题，但没有提前考虑。
至于你描述的结构有一些优点。例如，可以对表进行不同的索引或将其放置在不同的文件位置以提高性能。
但通常这些优点不值得保持结构同步更改（向不同表添加列以搜索两组依赖关系等）的成本成本。
如果您确实需要以不同方式对待它们，那么最好处理它通过添加一个带有视图的抽象层而不是创建两个单独的模型。
Any time I see a permenant table names "customer_temp" I see a red flag. This typically means that someone was working through a problem as they were going along and didn't think ahead about it.
As for the structure you describe there are some advantages. For example the tables could be indexed differently or placed on different File locations for performance.
But typically these advantages aren't worth the cost cost of keeping the structures in synch for changes (adding a column to different tables searching for two sets of dependencies etc. )
If you really need them to be treated differently then its better to handle that by adding a layer of abstraction with a view rather than creating two separate models.
正如您所建议的，我会使用单表设计。但我只知道你发布的有关此案的内容。在认定设计师是个白痴之前，我想知道两张桌子的设计可能会产生哪些有意或无意的其他后果。
例如，它可以减少存储新潜在客户的进程与访问现有客户群的进程之间的争用。或者，它可以允许某些列在客户表中被限制为不为空，而这些列在潜在客户表中被允许为空。或者，它可能允许严格控制对客户表的写访问，并且对于源自 Web 的操作不可用。
或者最初的设计师可能根本没有看到你我在单一表格设计中看到的好处。
I would have used a single table design, as you suggest. But I only know what you posted about the case. Before deciding that the designer was an idiot, I would want to know what other consequences, intended or unintended, may have followed from the two table design.
For, example, it may reduce contention between processes that are storing new potential customers and processes accessing the existing customer base. Or it may permit certain columns to be constrained to be not null in the customer table that are permitted to be null in the potential customer table. Or it may permit write access to the customer table to be tightly controlled, and unavailable to operations that originate from the web.
Or the original designer may simply not have seen the benefits you and I see in a single table design.