单个或多个数据库 [英] Single or multiple databases

问题描述

SQL Server 2008数据库设计问题。

SQL Server 2008 database design problem.

我定义了一种服务的体系结构，网站用户将在其拥有的多个网站上管理大量数据（平均100MB，每个站点最多1GB）。我正在考虑是否拆分数据库，使核心站点管理表（用户，付款，联系方式，登录详细信息，产品等）保存在一个数据库中，并且与客户自己的网站相关的数据库保存在单独的数据库。

I'm defining the architecture for a service where site users would manage a large volume of data on multiple websites that they own (100MB average, 1GB maximum per site). I am considering whether to split the databases up such that the core site management tables (users, payments, contact details, login details, products etc) are held in one database, and the database relating to the customer's own websites is held in a separate database.

我看到一个可能的收获，我可以分配硬件架构，以提供更多的肉在网站数据库中留下站点管理数据库一个更合适的区域。但我也意识到失去通过外键直接关联网站的客户的能力（据我所知，这不能跨数据库？）。

I am seeing a possible gain in that I can distribute the hardware architecture to provide more meat to the heavy lifting done in the websites database leaving the site management database in a more appropriate area. But I'm also conscious of losing the ability to directly relate the sites to the customers through a Foreign key (as far as I know this can't be done cross database?).

所以，问题是两个方面 - 一般来说，这种情况下的数据应该拆分成多个数据库，或者应该全部保存在单个数据库？

So, the question is two fold - in general terms should data in this sort of scenario be split out into multiple databases, or should it all be held in a single database?

如果它被拆分为多个，是否有一种推荐的方法来保护数据库层的系统的完整性和安全性，以确保在两个？

If it is split into multiple, is there a recommended way to protect the integrity and security of the system at the database layer to ensure that there is a strong relationship between the two?

感谢您的帮助。

推荐答案

因此我的答案可能接近主观的灰线，但至少我认为将管理表分成自己的数据库，这听起来像你在做什么是常见的做法。如果您可以将客户端绑定到特定的服务器和数据库实例，然后通过具有单独的数据库实例，它将打开一些简单的路径来添加服务器以添加客户端。如果你太大，单个数据库将需要你使用各种聚类方法。

This question and thus my answer may be close to the gray line of subjective, but at the least I think it would be common practice to separate out the 'admin' tables into their own db for what it sounds like you're doing. If you can tie a client to a specific server and db instance then by having separate db instances, it opens up some easy paths for adding servers to add clients. A single db would require you to monkey with various clustering approaches if you got too big.

早在每个客户端都获得自己的数据库为易于进行结构和组织变革时的发展奠定基调。发现2年从现在你需要做它会变得更加痛苦。我在过去很多次使用split dbs，并且真的不难处理，只要你能建立一个关于上下文的想法。在这里听起来你已经有了这样的想法，客户是上下文。

[edit]Building in the idea early that each client gets it's own DB also just sets the tone for how you develop when it is easy to make structural and organizational changes. Discovering 2 yrs from now you need to do it will become a lot more painful. I've worked with split dbs plenty of times in the past and it really isn't hard to deal with as long as you can establish some idea of what the context is. Here it sounds like you already have the idea that the client is the context.

只是我的两分钱，就像我说的，你可以接近主观的这一个。

Just my two cents, like I said, you could be close to subjective on this one.

这篇关于单个或多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！