多租户数据库设计-每个用户的数据库 [英] Multi-Tenant Database design - Database for each user

问题描述

我正在开发一个Web应用程序，它将要求用户拥有自己的一组私有数据.我最初的计划是创建一个stores表，一个users表和一个user_stores相交表.然后，我将在stores表中保存该商店的数据库名称(并使用应用程序用户和密码创建每个特定于商店的数据库，以便Web应用程序始终可以登录).

I am working on a web application that will require users to have their own set of private data. My original plan was to create a stores table, a users table, and a user_stores intersecting table. Then I would, in the stores table, save the database name for that store (and create each store-specific database with an application user and password so the web application could always login).

每个商店都将具有相似的数据(用户，产品，运输方式等)，我知道我可以使用外键引用将一个大型数据库中的所有内容绑定在一起.但是，由于数据是非常特定的并且可能是专有的，那么使用我的原始设计还是在其中包含每个人的数据的情况下创建单个数据库会更好吗?

Each store would have similar data (users, products, shipping methods, etc), and I know I can use foreign key references to tie everything together in one giant database. However, being that the data is very specific and potentially proprietary, would it be better to use my original design, or make a single database with everyone's data in there?

我正在考虑扩展关注点，最好是使用单独的数据库，因为我们可以将更活跃的帐户放在自己的(或更强大的)数据库服务器上，如果需要的话，只需在stores表中添加一个服务器位置字段.此外，它可能更安全，因为我们可以将用户登录信息添加到数据库中，并仅授予他们访问其数据的权限(防止一个用户编辑另一个用户的资料).我的问题是，是否有我遗失的担忧?我所读过的每一篇帖子都说不要使用我正在考虑的方法，而且我不是DBA.任何输入都会有所帮助.

I am thinking for scaling concerns, separate databases would be better because we could put the more active accounts on their own (or more powerful) database servers and simply add a server location field in the stores table if we needed to. Additionally, it may be more secure because we could make add the user login information to the database and only give them access to their data (preventing one user from editing another user's stuff). My question is, are there concerns that I am missing though? Just about every post I have read about this says not to use the method I am thinking of, and I am no DBA. Any input would be helpful.

其他信息: 这将托管在我将具有root访问权的专用服务器上.我可以根据需要创建任意数量的MySQL数据库.

Additional Information: This will be hosted on a Dedicated Server that I will have root access to. I can create as many MySQL databases as I need to.

推荐答案

我肯定会使用单个数据库.使用以下内容开始.使用单个数据库有几个原因，但是所有最大的原因是让您免于维护的噩梦.如果您必须更改架构，那么您将一团糟.

I would use a single database for sure. Use the following to get started. There are several reasons to go with a single db, however the biggest reason of all is to save you from a maintenance nightmare. If you have to change the schema, you will have a mess on your hands.

http://msdn.microsoft.com/en-us/library/aa479086.aspx

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