保持多个数据库的引用完整性 [英] Keeping referential integrity across multiple databases

问题描述

在多个数据库之间保持参照完整性的最佳做法是什么？因为没有内置函数

What are the best practices for keeping referential integrity across multiple databases? since there's no built-in functions

或者更好地分割单个数据库？

Or is it better to partition a single database?

更新
请参见下面的凯文示例。
这是我的情况。我的库存数据库具有引用employee数据库中employeeId的表。

Update See kevin's example below. that is my situation. My inventory database has tables that reference the employeeId in the employees database.

这些数据库目前维护在不同的位置（不同的服务器）

These databases are currently maintained at different locations (Different servers)

推荐答案

使用2k05 / 2k08，最好分割一个数据库。您可以将数据存储在多个数据库中，同时能够使用单个数据库的功能（如外键）具有所有优点。

With 2k05/2k08 it's definitely better to partition a single database. You have all the benefits of storing data like it is in multiple databases while being able to use the functions of a single database, like foreign keys.

据说，你不应该将所有内容都保存在一个数据库中。逻辑上，当组表不合在一起时，我通常将它们分成自己的数据库。例如，我不一定将订单系统数据库和员工管理数据库放在一起。我想可能有理由这样做，但我相信你可以在逻辑上分开数据存储区。

That being said, you shouldn't keep everything in a single database. Logically when groups tables don't fit together, I normally separate them into their own databases. For example, I wouldn't necessarily combine place an orders system database and the employee management database together. I suppose there could be reasons to do so, but I am sure you get my point of logically separating data stores where appropriate.

您应该查看的是两个数据库的交互程度。如果有很多字段可以加入到数据库中，那么我可以说这可能是一个好主意。如果它可能是连接到员工表的一个或两个字段，则可能不值得。您的其他选项是，如果连接数量很小，则将必要的表复制到清单数据库中，特别是如果它是一个表，而且两个现有的数据库很大且相当复杂。

What you should look at is how much the two databases interact. If there are lots of fields which would join across databases, then I would say that it is probably a good idea. If it's maybe one or two fields linking to the employee table, then it might not be worth doing. Your other option is, if the number of joins are small, is to duplicate the necessary tables into the inventory database, especially if it is one table and the two existing databases are large and rather complex.

这篇关于保持多个数据库的引用完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！