SQL设计周围缺乏跨数据库外键引用 [英] SQL design around lack of cross-database foreign key references

问题描述

无论好坏，我们都有一个依赖于多个数据库的解决方案，这些数据库都引用一个公共管理数据库。数据库作为模块的一部分提供，并不是所有的模块都是安装所必需的（可能为什么我们首先有多个数据库）。管理数据库是必需的，但是...所以它将永远在那里。

我想为混乱带来一些参照完整性和顺序，但是由于SQL服务器不能做跨数据库外键。在数据库中没有大量的流失，但是信息将被（技术）非技术用户插入/更新。

我的选择如下所示：

a）使用触发器强制伪外键一些工作）

b）使用触发器从管理员复制到其他数据库（明确的灾难配方）

d）不要担心它在DB级别，使用良好的UI设计，以确保没有人做任何愚蠢和限制访问/保持呼吸直接SQL访问。

坦率地说，我倾向于与D，但我想出去比我聪明的意见...

解决方案

假设每个模块设置，管理数据库，您可以通过为每个模块数据库中的管理表设置视图来简化操作。

For better or worse, we have a solution that relies on multiple databases that all reference a common administration database. Databases ship as part of modules, and not all modules are required for an installation (probably why we have multiple databases in the first place). The admin database is required, however ... so it will always be there.

I would like to bring some referential integrity and order to the chaos, but am stymied by SQL server's inability to do cross-database foreign keys. There is NOT a lot of churn in the database, but information will be inserted/updated by (ahem) non-technical users.

My choices as I see them are:

a) Impose pseudo foreign key using triggers (ok, but a bit of work)

b) Use triggers to replicate from admin to other databases (a clear recipe for disaster)

c) Impose psuedo foreign key in code / DAL (does not play well with ORM)

d) Don't worry about it at DB level, use good UI design to make sure no one does anything stupid and restrict access/hold breath on direct SQL access.

Frankly, I'm inclined to go with "D", but figured I'd go out for opinions smarter than me ...

解决方案

Assuming each module is set up so that it's linked with the administration database, you might be able to simplify thing by setting up views for the administration tables within each module database.

这篇关于SQL设计周围缺乏跨数据库外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！