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

问题描述

无论好坏，我们都有一个依赖于多个数据库的解决方案，这些数据库都引用了一个通用的管理数据库.数据库作为模块的一部分提供，并非所有模块都需要安装(这可能是我们首先拥有多个数据库的原因).需要管理数据库，但是......所以它会一直存在.

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.

我想为混乱带来一些参照完整性和秩序，但由于 SQL Server 无法执行跨数据库外键而受到阻碍.数据库中没有很多流失，但信息将由(咳咳)非技术用户插入/更新.

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.

我的选择是:

a) 使用触发器强加伪外键(好的，但有点工作)

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

b) 使用触发器从管理员复制到其他数据库(灾难的明显秘诀)

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

c) 在代码/DAL 中施加伪外键(不能很好地与 ORM 配合使用)

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

d) 不要在 DB 级别担心它，使用良好的 UI 设计来确保没有人做任何愚蠢的事情并限制直接 SQL 访问的访问/屏住呼吸.

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.

坦率地说，我倾向于选择D"，但我认为我会比我更聪明地寻求意见......

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屋！