与多个数据库具有并属于多种关系

发布于 2024-07-07 03:43:33 字数 334 浏览 11 评论 0原文

我遇到的情况是,我有两个模型:公司和权限,其中公司位于与我的权限数据库不同的数据库中。 这是一种拥有和属于多个关系,因为每个公司可以拥有多个权限,每个权限可以属于多个公司。

拆分两个数据库的原因是公司数据库运行高要求的生产应用程序,而权限数据库控制另一个应用程序的权限。

使用rails,它会在与主表相同的数据库中查找连接表。 例如,如果我执行company.permissions,它会在公司数据库中查找company_permissions。 如果我执行permission.companies,它会在权限数据库中查找。

使用具有和属于多个数据库的多个关系的最佳解决方案是什么?

I have a situation where I have two models, companies and permissions, where companies is in a separate database from my permissions database. This is a has and belongs to many relationship because each company can have many permissions and each permission can belong to many companies.

The reason the two databases are split is because the company database runs a high demand production application and the permissions database controls the permissions for another application.

With rails, it looks for the join table in the same database as the primary table. For instance, if I do company.permissions, it looks in the company database for company_permissions. If I do permission.companies it looks in the permissions database.

What is the best solution to using a has and belongs to many relationship with multiple databases?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

幻想少年梦 2024-07-14 03:43:33

拥有并属于许多旧的、笨重的和有问题的。 我建议使用 has_many 代替。
可以用“:through”选项指定关系表; 只需选择您希望它驻留在哪个数据库并创建一个模型来表示它。
http://apidock.com/rails/ActiveRecord/Associations/ClassMethods/has_many

Has and belongs to many is old, clunky, and problematic. I recommend using has_many instead.
You can specify the relationship table with the ":through" option; just pick which database you want it to reside in and create a model to represent it.
http://apidock.com/rails/ActiveRecord/Associations/ClassMethods/has_many

哆兒滾 2024-07-14 03:43:33

有趣的问题。 为了供我自己参考,让我尝试总结一下《Rails Recipe》书中在这个问题的背景下提出的解决方案。

1) 首先添加database.yml

permissions:
  adapter: mysql
  database: permissions
  username: root
  password: 
  socket: /tmp/mysql.sock

2) 创建权限模型以调用外部数据库

class Permission < ActiveRecord::Base

  establish_connection :permissions

end 

3) 创建(迁移)带有权限ID权限引用表 > 列

4) 使用 PermissionReference 模型作为 Permission 模型的链接

class PermissionReference < ActiveRecord::Base

  belongs_to :permission
  has_and_belongs_to_many :companies,
                          :join_table => 'companies_permissions',
                          :foreign_key => 'permission_id'

end

5) 最后将 Company 与 Permission 相关联

class Company < ActiveRecord::Base

  has_and_belongs_to_many :permissions, 
                          :class_name => 'PermissionReference', 
                          :join_table => 'companies_permissions', 
                          :association_foreign_key => 'permission_id'

end

您可能想考虑通过子类化调用外部数据库的模型来进行重构

class External < ActiveRecord::Base

  self.abstract_class = true
  establish_connection :permissions

end

class Permission < External
end

Interesting question. For the sake of my own reference, let me try to summarize the solution proposed in the Rails Recipe book in this question's context.

1) First add in database.yml

permissions:
  adapter: mysql
  database: permissions
  username: root
  password: 
  socket: /tmp/mysql.sock

2) Make Permission model to call external database

class Permission < ActiveRecord::Base

  establish_connection :permissions

end 

3) Create (migrate) a Permission Reference table with Permission Id column

4) Use PermissionReference model as a link to Permission model

class PermissionReference < ActiveRecord::Base

  belongs_to :permission
  has_and_belongs_to_many :companies,
                          :join_table => 'companies_permissions',
                          :foreign_key => 'permission_id'

end

5) Lastly associate Company to Permission

class Company < ActiveRecord::Base

  has_and_belongs_to_many :permissions, 
                          :class_name => 'PermissionReference', 
                          :join_table => 'companies_permissions', 
                          :association_foreign_key => 'permission_id'

end

You might wanna consider refactoring by subclassing models that calls an external database

class External < ActiveRecord::Base

  self.abstract_class = true
  establish_connection :permissions

end

class Permission < External
end
鲜肉鲜肉永远不皱 2024-07-14 03:43:33

如果公司数据不经常变化,也许你可以将数据同步到权限数据库中,然后自然地进行加入。 我继承的 CMS 正在做类似的事情,其中​​用户身份验证位于其自己的数据库中,权限等都存储在那里,并且用户帐户同步到每个站点的本地数据库。 这样,用户帐户可以通过 1 次登录在多个站点之间共享(尤其是用于管理)。

可能不是最好的,因为涉及同步。 理想情况下,如果您无法将权限移出,您应该将公司数据存储在权限数据库中。 当然,除非您在其他地方加入公司。

If the companies data does not change very often, perhaps you could synchronize the data into the permissions database, then do your join naturally. A CMS I inherited is doing something like that where the user authentication is in its own database, and the permissions and such are all stored there, and user accounts are synch'd to the local database for each site. This way user accounts could be shared (especially for administration) across multiple sites with 1 login.

May not be the best because a synch is involved. Ideally you should just store the company data in the permissions db if you can't move permissions out. Unless of course you are joining on company elsewhere.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文