需要更新我的单用户应用数据库以允许多个用户，如何修改数据库模式？ [英] Need to update my single user app database to allow multiple users, how to modify database schema?

问题描述

我为一个本地小组建立了一个应用程式，不久之后就开始使用飞盘联盟。我想建立这个，并使它成为我可以提供给其他团体。

Jist：用户注册播放，应用程式建立小组，应用程式建立时间表，每个小组都有管理员可以管理他们的联赛。

问题是，我有一个数据库，它最初设置为只处理一个组的联赛。我将如何重做架构，以允许多个组只能登录和修改自己的联赛。

我的想法是创建一个帐户表，并将account_id粘贴到每个表。这是一个好的方法吗？我已经附加了模式，所以你应该能够看到我到目前为止！

注意：我正在Codeigniter（php）和MySQL。 p>

解决方案

多个客户端;一个托管应用程序。

当您构建多租户数据库时，您需要考虑

• 查询


• 费用


• 数据隔离和保护

多租户解决方案的范围从每个租户一个数据库没有共享）到每个租户一行（共享一切）。

无共享，单独数据库或每个租户一个数据库

• 每个客户最贵。 （大量的客户端意味着大量的服务器。）


• 最高的数据隔离度。


• 单个租户的灾难恢复非常简单直接。


• 理论上维护更难，因为需要在每个数据库中进行更改。但是dbms可能很容易支持在每个数据库中运行存储过程。 （SQL Server有一个未记录的系统存储过程，例如sp_msforeachdb，你可以写自己的。）Shared nothing也是最容易定制的，但也会引起更多的维护问题。


• 每个表的最少行数。查询速度接近最佳。

共享一切或共享模式

• 每个租户最贵。


• 最低的数据隔离度。每个表都有一个列，用于标识行所属的租户。由于租户行在每个表中混合，因此意外暴露其他租户的数据相对较为简单。


• 单个租户的灾难恢复相对复杂;您必须在多个表中还原个别行。


• 由于所有租户共享表，结构维护更简单。然而，它增加了通信负载，因为您必须与每个租户沟通和协调每个更改。


• 每个表格的最多行数。快速查询更难，但它取决于多少租户和多少行。

在无共享和共享的一切之间是共享模式 p>

共享模式

• 租户共享数据库，但每个租户都有自己的命名模式。成本落在无共享和共享一切之间;大系统通常比无共享需要更少的服务器，比共享一切更多的服务器。


• 比共享一切更好的隔离。不如没有共享的隔离。 （您可以对模式使用GRANT和REVOKE权限。）


• 单个租户的灾难恢复需要恢复多个模式中的一个。这是相对容易或相当困难，取决于你的dbms。


• 维护比shared nothing容易;不像共享一切那么容易。编写将在数据库中的每个模式中执行的存储过程是相对简单的。


• 通常每个服务器的活跃用户比无共享多，这意味着他们共享（降级）更多的资源。但是不如共享一切那么糟糕。

Microsoft有一篇关于多租户架构与更多详细信息。 （链接只是多页文档的一页。）

I built an app for a local group who runs frisbee leagues not too long ago. I am wanting to build this out, and make it something that I can provide to other groups.

Jist: people sign up to play, app creates teams, app creates schedules, there are admins for each group who can manage their leagues

The problem is, I have one database, and it was originally set up to only deal with one group's leagues. How would I go about reworking the schema to allow multiple groups to be able to log in and modify only their own leagues.

My thought was to create an "account" table, and tack "account_id" to every table. Is this a good approach? I have attached the schema, so you should be able to see what I have thus far!

Note: I am building this in Codeigniter (php) and MySQL.

解决方案

Multiple clients; one hosted application. You're describing a multi-tenant database.

When you build a multi-tenant database, you need to consider

• querying
• cost
• data isolation and protection
• maintenance, and
• disaster recovery.

Multi-tenant solutions range from one database per tenant (shared nothing) to one row per tenant (shared everything).

"Shared nothing", "separate database", or one database per tenant

• Most expensive per client. (Large numbers of clients imply large numbers of servers.)
• Highest degree of data isolation.
• Disaster recovery for a single tenant is simple and straightforward.
• Maintenance is theoretically harder, because changes need to be carried out in every database. But your dbms might easily support running stored procedures in each database. (SQL Server has an undocumented system stored procedure, sp_msforeachdb, for example. You can probably write your own.) "Shared nothing" is the most easily customizable, too, but that also raises more maintenance issues.
• Lowest number of rows per table. Querying speed is near optimal.

"Shared everything", or "shared schema", or "one database per planet"

• Least expensive per tenant.
• Lowest degree of data isolation. Every table has a column that identifies which tenant a row belongs to. Since tenant rows are mixed in every table, it's relatively simple to accidentally expose other tenant's data.
• Disaster recovery for a single tenant is relatively complicated; you have to restore individual rows in many tables.
• Structural maintenance is simpler, given that all tenants share the tables. It increases the communication load, though, because you have to communicate and coordinate each change with every tenant. It's not easily customizable.
• Highest number of rows per table. Quick querying is harder, but it depends on how many tenants and how many rows. You could easily tip over into VLDB territory.

Between "shared nothing" and "shared everything" is "shared schema".

"Shared schema"

• Tenants share a database, but each tenant has it's own named schema. Cost falls between "shared nothing" and "shared everything"; big systems typically need fewer servers than "shared nothing", more servers than "shared everything".
• Much better isolation than "shared everything". Not quite as much isolation as "shared nothing". (You can GRANT and REVOKE permissions on schemas.)
• Disaster recovery for a single tenant requires restoring one of many schemas. This is either relatively easy or fairly hard, depending on your dbms.
• Maintenance is easier than "shared nothing"; not as easy as "shared everything". It's relatively simple to write a stored procedure that will execute in each schema in a database. It's easier to share common tables among tenants than with "shared nothing".
• Usually more active tenants per server than "shared nothing", which means they share (degrade) more resources. But not as bad as "shared everything".

Microsoft has a good article on multi-tenant architecture with more details. (The link is to just one page of a multi-page document.)

这篇关于需要更新我的单用户应用数据库以允许多个用户，如何修改数据库模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！