每个应用程序的数据库VS一个很大的数据库，所有的应用程序 [英] Database per application VS One big database for all applications

问题描述

我设计将共享2或3的数据库表的几个应用程序和所有其他表的将独立的每个应用的。共享数据库主要包含用户信息，并有可能发生在其他表需要共享的情况下，但是这是我的直觉说话。

I'm designing a few applications that will share 2 or 3 database tables and all of the other tables will be independent of each app. The shared databases contain mostly user information, and there might occur the case where other tables need to be shared, but that's my instinct speaking.

我俯身为所有应用的解决方案之一数据库，因为我想有引用完整性，我不会要保持相同的信息保持最新在每个数据库，但我可能会结束与100+的表的数据库，其中只有十张桌子的群体都会有相关的信息。

I'm leaning over the one database for all applications solution because I want to have referential integrity, and I won't have to keep the same information up to date in each of the databases, but I'm probably going to end with a database of 100+ tables where only groups of ten tables will have related information.

按应用方法的数据库可以帮助我把一切都更加有组织的，但我不知道的方式，以保持在所有数据库中相关的表是最新的。

The database per application approach helps me keep everything more organized, but I don't know a way to keep the related tables in all databases up to date.

所以，基本的问题是：这两种方法你推荐

So, the basic question is: which of both approaches do you recommend?

谢谢，

豪尔赫·巴尔加斯。

编辑1：

当我谈论不能够有参照完整性，这是因为没有办法在表的外键时，这些表在不同的数据库和每个应用程序的表至少有一个需要一个外键一的共享表

When I talk about not being able to have referential integrity, it's because there's no way to have foreign keys in tables when those tables are in different databases, and at least one of the tables per application will need a foreign key to one of the shared tables.

编辑2：

链接到相关的问题：

• http://stackoverflow.com/questions/265921/sql-design-around-lack-of-cross-database-foreign-key-references


• http://stackoverflow.com/questions/960465/keeping-referential-integrity-across-multiple-databases


• http://stackoverflow.com/questions/223809/how-to-salvage-referential-integrity-with-mutiple-databases

只有第二个有一个公认的答案。还没有决定做什么。

Only the second one has an accepted answer. Still haven't decided what to do.

答：

我已经决定去与每个应用程序一起共享数据库跨数据库的引用的数据库，添加视图为每个数据库中模仿共享数据库表，并使用NHibernate作为我的ORM。由于会员制我将使用asp.net之一。

I've decided to go with a database per application with cross-database references to a shared database, adding views to each database mimicking the tables in the shared database, and using NHibernate as my ORM. As the membership system I'll be using the asp.net one.

我还将使用触发器和逻辑删除，要尽力保持到最低ID的数量我会到处飞还活着的话在疯狂没有父。保持同步数据库所需的开发工作太多，回报太少（大家都已经指出）。所以，我宁愿通过孤立的记录打我的方式。

I'll also use triggers and logical deletes to try and keep to a minimum the number of ID's I'll have flying around livin' la vida loca without a parent. The development effort needed to keep databases synced is too much and the payoff is too little (as you all have pointed out). So, I'd rather fight my way through orphaned records.

由于使用ORM和视图最初是由svinto建议，他得到正确的答案。

Since using an ORM and Views was first suggested by svinto, he gets the correct answer.

感谢所有帮助我这个艰难的决定。

Thanks to all for helping me out with this tough decision.

推荐答案

这取决于你的选择有点不同取决于数据库和你使用的框架。我建议你​​使用某种ORM，并且这样你就不需要理会那么多。不管怎么说，你可以把大概每个应用程序在它自己的模式在数据库中，然后无论是schemaname.tablename引用共享表或创建这只是每个应用模式的 SELECT * FROM schemaname.tablename 然后code反对这种观点。

It depends and your options are a bit different depending on the database and frameworks you're using. I'd recommend using some sort of ORM and that way you don't need to bother that much. Anyways you could probably put each app in it's own schema in the database and then either reference the shared tables by schemaname.tablename or create views in each application schema that's just a SELECT * FROM schemaname.tablename and then code against that view.

这篇关于每个应用程序的数据库VS一个很大的数据库，所有的应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！