如何使用多个数据库挽回引用完整性 [英] How to salvage referential integrity with mutiple databases

问题描述

我正在设计一个系统，用于在全国各地饲养多个生产站点（所有信息都在一个站点中），有可能添加更多信息。最初我以为我只能使用一个数据库才能摆脱。我现在正在重新思考我的原始设计，并倾向于一个更具扩展性的解决方案。保持每个数据库/表的大小也很重要。

I am in the middle of designing a system that will be used to feed several production sites around the country (all information is in one site) with the potential to add more. Initially I thought that I could get away with only using one database. I am now re-thinking my original design and leaning toward a more scalable solution. Keeping down the size of each database / tables is also important.

将有一个主数据库，其中包含跨站点概念的信息，然后单独每个站点的数据库中包含站点特定的信息。

There will be a "master" database that has information that spans the notion of a site and then a separate database for each site with site-specific information in it.

我的努力是分离数据的地方。数据都是相当相关的。无论我在哪里，都会失去一些参照诚信。我读过的一切都是为了避免这一切，我认为是非常好的理由，但我没有看到这个方法。

My struggle is where to separate the data. The data is all fairly related. No matter where I do it I will lose some referential integrity. Everything I've read says to avoid this at all costs for what I think are very good reasons, but I don't see a way around it.

我已经看了进入触发器，但是如果数据库位于不同的服务器上，我不认为它们工作（不确定 - 我认为Oracle执行此操作）。我只限于一个开源解决方案，所以如果这有帮助，那么它将是MySQL或postgre。

I have looked into triggers, but I don't think that they work if the databases are on separate servers (not sure though - I think Oracle does this). I am limited to an open source solution so it'll be MySQL or postgre if that helps at all.

有没有人有一些建议来减轻这个问题或有另一个设计建议？

Does anybody have some suggestions to mitigate this problem or have another design suggestion?

推荐答案

不知道更多关于你的具体情况，这有点难以帮助 - 但这是我的直觉...

Without knowing more about your specific situation, it's a little difficult to help - but here's my gut feeling...

我猜测您建议的信息应该放在主数据库中可能更有可能是稳定的（数据的数量很少）比每个站点的数据库。

I'm guessing that the information that you have suggested should go in your 'Master' database is perhaps more likely to be stable (a low number of changes to the data) than the databases for each site.

也许您可以查看主数据库中的数据也存储在每个站点的数据库中的解决方案。然后，您可以查看某种复制系统，将对master数据库的更改传播到站点数据库。

Perhaps you could look at a solution where the data in the 'Master' database is also stored in each site's database. Then you could look at some sort of replication system to propagate changes that are made to the master database down to the site databases.

这样，您仍然可以维护引用每个站点数据库内的完整性。

That way, you can still maintain the referential integrity within each site's database.

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