I think you should consider not making references in database layer for cross-application relations, and make them in application layer. That would allow you to split it to one database per app.
I'm working on one app with 100+ tables. I have them in one database, and are separated by prefixes - each table has prefix for module it belongs to. Then i have built a layer on top of database functions to use this custom groups. I'm also building data administrator, which takes advantage of this table groups and makes editing data very easy.
这取决于您使用的数据库和框架，您的选项会有所不同。我建议使用某种 ORM，这样你就不需要那么麻烦。无论如何，您可能可以将每个应用程序放入数据库中自己的架构中，然后通过 schemaname.tablename 引用共享表，或者在每个应用程序架构中创建视图，这只是一个 SELECT * FROM schemaname.tablename ，然后反对该观点的代码。
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.
There are no hard and fast rules to choose one over the other.
Multiple databases provide modularity. As far as sync-ing across multiple databases are concerned, one can use the concept of linked servers and views thereof and can gain the advantages of integrated database (unified access) as well.
Also, keeping multiple databases can help better management of security, data, backup & restore, replication, scaling out etc!
THat does not sound like "a lot of applications" at all, but like "one application system with different executables". Naturally they can share one database. Make smart usage of Schemata to isolate the different funcational areas within one database.
One database for all application in my opinion .Data would be stored once no repitation.
With the other approach you would end up replicating and in my opinion when you start replicating it will bring its own headache and data would go out of sync too
The most appropriate approach from scalability and maintenence point of view would be to make the "shared/common" tables subset self-sufficient and put it to "commons" database, for all others have 1 db per application of per logical scope (business logic determined) and maintain this structure always
This will ease the planning and execution commissioning/decommissioning/relocation/maintenence procedures of your software (you will know exactly which two affected DBs (commons+app_specific) are involved if you know which app you are going to touch and vice versa.
DECLARE @ref (datatype appropriate to your field)
SELECT @ref = refField FROM inserted
IF NOT EXISTS (SELECT *
FROM referenceserver.refDB.dbo.refTable
WHERE refField = @ref)
BEGIN
RAISERROR(...)
ROLLBACK TRAN
END
要进行多行插入和更新，您可以在引用字段上连接表，但这可能会非常慢。
At our business, we went with a separate database per application, with cross database references for the small amount of shared information and an occasional linked server. This has worked pretty well with a development, staging, build and production environments.
For users, our entire user base is on windows. We use Active Directory to manage the users with application references to groups, so that the apps don't have to manage users, which is nice. We did not centralize the group management, that is each application has tables for groups and security which is not so nice but works.
I would recommend, that if your applications are really different, to have a database per application. Looking back, the central shared database for users sounds workable as well.
You can use triggers for cross database referential integrity:
Create a linked server to the server that holds the database that you want to reference. Then use 4-part naming to reference the table in the remote database that holds the reference data. Then put this in the insert and update triggers on the table.
EXAMPLE(assumes single row inserts and updates):
DECLARE @ref (datatype appropriate to your field)
SELECT @ref = refField FROM inserted
IF NOT EXISTS (SELECT *
FROM referenceserver.refDB.dbo.refTable
WHERE refField = @ref)
BEGIN
RAISERROR(...)
ROLLBACK TRAN
END
To do multi row inserts and updates you can join the tables on the reference field but it can be very slow.
I think the answer to this question depends entirely on your non functional requirements. If you are designing a application that will one day need to be deployed across 100's of nodes then you need to design your database so that if need be it could be horizontally scaled. If on the other hand this application is to be used by a hand full of users and may have a short shelf life then you approach will be different. I have recently listened to a pod cast of how EBAY's architecture is set-up, http://www.se-radio.net/podcast/2008-09/episode-109-ebay039s-architecture-principles-randy-shoup, and they have a database per application stream and they use sharding to split tables across physical nodes. Now their non-functional requirements are that the system is available 24/7, is fast, can support thousands of users and that is does not lose any important data. EBAY make millions of pounds and so can support the effort that this takes to develop and maintain.
Anyway this does not answer your question:) my personnel opinion would be to make sure your non-functional requirements have been documented and signed off by someone. That way you can decide on the best Architecture. I would be tempted to have each application using its own database and a central database for shared data. And I would try to minimise the dependencies between them, which I'm sure is not easy or you would have done it:), but I would also try to steer clear of having to produce some sort of middle ware software to keep tables in sync as this could create a headaches for you.
At the end of the day you need to get your system up and running and the guys with the pointy hair wont give a monkeys chuff about how cool your design is.
We went for splitting the database down, and having one common database for all the shared tables. Due to them all being on the save SQL Server instance it didn't affect the cost of running queries across multiple database.
The key in replication for us was that whole server was on a Virtual Machine (VM), so for replication to create Dev/Test environments, IT Support would just create a copy of that image and restore additional copies when required.
发布评论
评论（9）
这两种方式看起来都不理想，
我认为您应该考虑不在数据库层中对跨应用程序关系进行引用，而在应用程序层中进行引用。这将允许您将其拆分为每个应用程序一个数据库。
我正在开发一款包含 100 多个表的应用程序。我将它们放在一个数据库中，并用前缀分隔 - 每个表都有其所属模块的前缀。然后我在数据库函数之上构建了一个层来使用这个自定义组。我还在构建数据管理器，它利用此表组并使编辑数据变得非常容易。
Neither way looks ideal
I think you should consider not making references in database layer for cross-application relations, and make them in application layer. That would allow you to split it to one database per app.
I'm working on one app with 100+ tables. I have them in one database, and are separated by prefixes - each table has prefix for module it belongs to. Then i have built a layer on top of database functions to use this custom groups. I'm also building data administrator, which takes advantage of this table groups and makes editing data very easy.
这取决于您使用的数据库和框架，您的选项会有所不同。我建议使用某种 ORM，这样你就不需要那么麻烦。无论如何，您可能可以将每个应用程序放入数据库中自己的架构中，然后通过 schemaname.tablename 引用共享表，或者在每个应用程序架构中创建视图，这只是一个
SELECT * FROM schemaname.tablename，然后反对该观点的代码。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.tablenameand then code against that view.没有硬性规定可以选择其中之一。
多个数据库提供模块化。就跨多个数据库的同步而言，可以使用链接服务器及其视图的概念，并且还可以获得集成数据库（统一访问）的优势。
此外，保留多个数据库可以帮助更好地管理安全、数据、备份和数据。恢复、复制、横向扩展等！
我的2美分。
There are no hard and fast rules to choose one over the other.
Multiple databases provide modularity. As far as sync-ing across multiple databases are concerned, one can use the concept of linked servers and views thereof and can gain the advantages of integrated database (unified access) as well.
Also, keeping multiple databases can help better management of security, data, backup & restore, replication, scaling out etc!
My 2cents.
这听起来根本不像“很多应用程序”，而是像“具有不同可执行文件的一个应用程序系统”。当然，他们可以共享一个数据库。巧妙地使用 Schemata 来隔离一个数据库中的不同功能区域。
THat does not sound like "a lot of applications" at all, but like "one application system with different executables". Naturally they can share one database. Make smart usage of Schemata to isolate the different funcational areas within one database.
我认为一个数据库适用于所有应用程序。数据将被存储一次，不会重复。
使用另一种方法，您最终会进行复制，在我看来，当您开始复制时，它会带来麻烦，并且数据也会不同步
One database for all application in my opinion .Data would be stored once no repitation.
With the other approach you would end up replicating and in my opinion when you start replicating it will bring its own headache and data would go out of sync too
从可扩展性和维护的角度来看，最合适的方法是使“共享/通用”表子集自给自足，并将其放入“公共”数据库，因为所有其他数据库的每个逻辑范围（业务逻辑）的每个应用程序都有 1 db确定）并始终维护此结构
这将简化软件的规划和执行调试/退役/搬迁/维护过程（如果您知道要使用哪个应用程序，您将确切地知道涉及哪两个受影响的数据库（commons+app_specific）触摸，反之亦然。
The most appropriate approach from scalability and maintenence point of view would be to make the "shared/common" tables subset self-sufficient and put it to "commons" database, for all others have 1 db per application of per logical scope (business logic determined) and maintain this structure always
This will ease the planning and execution commissioning/decommissioning/relocation/maintenence procedures of your software (you will know exactly which two affected DBs (commons+app_specific) are involved if you know which app you are going to touch and vice versa.
在我们的业务中，我们为每个应用程序使用单独的数据库，并为少量共享信息提供跨数据库引用，并且偶尔使用链接服务器。这在开发、暂存、构建和生产环境中运行得非常好。
对于用户来说，我们整个用户群都在windows上。我们使用 Active Directory 通过应用程序对组的引用来管理用户，这样应用程序就不必管理用户，这很好。我们没有集中组管理，即每个应用程序都有组和安全性表，虽然不太好但有效。
我建议，如果您的应用程序确实不同，那么每个应用程序都有一个数据库。回顾过去，用户的中央共享数据库听起来也可行。
您可以使用触发器来实现跨数据库引用完整性：
创建一个指向保存要引用的数据库的服务器的链接服务器。然后使用 4 部分命名来引用远程数据库中保存引用数据的表。然后将其放入表上的插入和更新触发器中。
示例（假设单行插入和更新）：
要进行多行插入和更新，您可以在引用字段上连接表，但这可能会非常慢。
At our business, we went with a separate database per application, with cross database references for the small amount of shared information and an occasional linked server. This has worked pretty well with a development, staging, build and production environments.
For users, our entire user base is on windows. We use Active Directory to manage the users with application references to groups, so that the apps don't have to manage users, which is nice. We did not centralize the group management, that is each application has tables for groups and security which is not so nice but works.
I would recommend, that if your applications are really different, to have a database per application. Looking back, the central shared database for users sounds workable as well.
You can use triggers for cross database referential integrity:
Create a linked server to the server that holds the database that you want to reference. Then use 4-part naming to reference the table in the remote database that holds the reference data. Then put this in the insert and update triggers on the table.
EXAMPLE(assumes single row inserts and updates):
To do multi row inserts and updates you can join the tables on the reference field but it can be very slow.
我认为这个问题的答案完全取决于您的非功能需求。如果您正在设计一个应用程序，有一天需要跨 100 个节点部署，那么您需要设计数据库，以便在需要时可以水平扩展。另一方面，如果该应用程序由大量用户使用并且保质期可能很短，那么您的方法将会有所不同。我最近听了一个有关如何设置 EBAY 架构的播客，http://www.se-radio.net/podcast/2008-09/episode-109-ebay039s-architecture-principles-randy-shoup，他们有每个应用程序流一个数据库，他们使用分片来跨物理节点拆分表。现在他们的非功能性要求是系统24/7可用、速度快、可以支持数千个用户并且不会丢失任何重要数据。 EBAY 赚取数百万英镑，因此可以支持开发和维护所需的努力。
无论如何，这并不能回答您的问题:)我的个人意见是确保您的非功能性需求已被记录并由某人签署。这样您就可以决定最好的架构。我很想让每个应用程序使用自己的数据库和一个用于共享数据的中央数据库。我会尝试最小化它们之间的依赖关系，我确信这并不容易，否则你会做到的:)，但我也会尝试避免必须生产某种中间件软件来保存表同步，因为这可能会让您感到头疼。
一天结束时，你需要让你的系统启动并运行，而那些尖尖头发的家伙不会对你的设计有多酷感到高兴。
I think the answer to this question depends entirely on your non functional requirements. If you are designing a application that will one day need to be deployed across 100's of nodes then you need to design your database so that if need be it could be horizontally scaled. If on the other hand this application is to be used by a hand full of users and may have a short shelf life then you approach will be different. I have recently listened to a pod cast of how EBAY's architecture is set-up, http://www.se-radio.net/podcast/2008-09/episode-109-ebay039s-architecture-principles-randy-shoup, and they have a database per application stream and they use sharding to split tables across physical nodes. Now their non-functional requirements are that the system is available 24/7, is fast, can support thousands of users and that is does not lose any important data. EBAY make millions of pounds and so can support the effort that this takes to develop and maintain.
Anyway this does not answer your question:) my personnel opinion would be to make sure your non-functional requirements have been documented and signed off by someone. That way you can decide on the best Architecture. I would be tempted to have each application using its own database and a central database for shared data. And I would try to minimise the dependencies between them, which I'm sure is not easy or you would have done it:), but I would also try to steer clear of having to produce some sort of middle ware software to keep tables in sync as this could create a headaches for you.
At the end of the day you need to get your system up and running and the guys with the pointy hair wont give a monkeys chuff about how cool your design is.
我们对数据库进行了拆分，并为所有共享表建立了一个公共数据库。由于它们都位于保存的 SQL Server 实例上，因此不会影响跨多个数据库运行查询的成本。
对我们来说，复制的关键是整个服务器都位于虚拟机 (VM) 上，因此对于创建开发/测试环境的复制，IT 支持人员只需创建该映像的副本并在需要时恢复其他副本。
We went for splitting the database down, and having one common database for all the shared tables. Due to them all being on the save SQL Server instance it didn't affect the cost of running queries across multiple database.
The key in replication for us was that whole server was on a Virtual Machine (VM), so for replication to create Dev/Test environments, IT Support would just create a copy of that image and restore additional copies when required.