NoSQL / RDBMS混合引用完整性（删除级联）？ [英] NoSQL / RDBMS hybrid with referential integrity (delete cascade)?

问题描述

有没有一个数据库，给你的参照完整性的好处，并能够使用SQL类型语言进行查询，还允许实体相对于其数据属性以及它们之间的关系进行宽松定义？ / p>

例如采用RBAC类型模型，其中您有权限，用户，用户组&角色。复杂/灵活的模型可以有以下规则：

• 角色可以有一个或多个权限，权限可以属于一个或多个角色


• 用户可以拥有一个或多个权限，并且权限可以属于一个或多个用户


• 用户组可以拥有一个或多个权限和权限可以属于一个或多个用户组


• 用户可以拥有一个或多个角色，角色可以属于一个或多个用户


• 拥有一个或多个角色，角色可以属于一个或多个用户组


• 角色可以有一个或多个角色，角色可以属于一个或多个角色

要在RDBMS中对上述进行建模，需要创建大量的交叉表。理想情况下，我想在数据库中定义的是实体自身（用户，角色等）加上一些强制属性。其他一切将是动态的（即不需要DDL），例如。我可以创建一个新的属性没有预定义的用户。我还可以在未预定义的实体之间创建关系，尽管数据库将像正常的RDBMS一样处理参照完整性。

上面可以在一定程度上实现在RDBMS中通过创建一个表来存储实体，另一个用来存储关系等，但这会使执行简单查询所需的SQL过于复杂，并且也可能会对性能产生影响。

解决方案

大多数NoSQL数据库构建得非常好。这是以一致性为代价的，参照完整性是其一部分。因此，大多数NoSQL不支持任何类型的关系约束。

有一种类型的NoSQL数据库支持关系。实际上，它专为关系而设计：图形数据库。图形数据库存储节点和这些节点之间的显式关系（边）。节点和边都可以包含键/值对形式的数据，而不绑定到预定义的模式。

图数据库针对关系查询和漂移图操作进行了优化，例如找到两个节点之间的最短路径，或找到距离当前节点给定距离内的所有节点。你不需要在角色/权限场景中使用它，但如果你这样做，使用RDBMS实现起来会更困难。

另一种选择是使您的整个数据层是混合的，通过使用RDBMS来存储关系和文档数据库来存储实际数据。这会使您的应用程序稍微复杂，但我不认为这是一个坏的解决方案。您将使用两种不同的技术，两者都处理他们设计处理的问题。

Is there a database out there that gives you the benefit of referential integrity and being able to use a SQL type language for querying, but also lets entities be loosely defined with respect to their data attributes and also the relationships between them?

E.g. take a RBAC type model where you have Permissions, Users, User Groups & Roles. A complex/flexible model could have the following rules:

• Roles can have one or more permissions and a permission can belong to one or more Roles
• Users can have one or more permissions and a permission can belong to one or more Users
• Users Groups can have one or more permissions and a permission can belong to one or more Users Groups
• Users can have one or more roles and a role can belong to one or more Users
• User Groups can have one or more roles and a role can belong to one or more User Groups
• Roles can have one or more roles and a role can belong to one or more Roles

To model the above in an RDBMS would involve the creation of lots of intersection tables. Ideally, all I'd like to define in the database is the entities themselves (User, Role, etc) plus some mandatory attributes. Everything else would then be dynamic (i.e. no DDL required), e.g. I could create a User with a new attribute which wasn't pre-defined. I could also create a relationship between entities that hasn't been predefined, though the database would handle referential integrity like a normal RDBMS.

The above can be achieved to some degree in a RDBMS by creating a table to store entities and another one to store relationships etc, but this overly complicates the SQL needed to perform simple queries and may also have performance implications.

解决方案

Most NoSQL databases are built to scale very well. This is done at the cost of consistency, of which referential integrity is part of. So most NoSQL don't support any type of relational constraints.

There's one type of NoSQL database that does support relations. In fact, it's designed especially for relations: the graph database. Graph databases store nodes and explicit relations (edges) between these nodes. Both nodes and edges can contain data in the form of key/value pairs, without being tied to a predefined schema.

Graph databases are optimized for relational queries and nifty graph operations, such as finding the shortest path between two nodes, or finding all nodes within a given distance from the current node. You wouldn't need this in a role/permission scenario, but if you do, it'll be a lot harder to achieve using an RDBMS.

Another option is to make your entire data layer a hybrid, by using a RDBMS to store the relations and a document database to store the actual data. This would complicate your application slightly, but I don't think it's such a bad solution. You'll be using two different technologies, both dealing with the problems they were designed to deal with.

这篇关于NoSQL / RDBMS混合引用完整性（删除级联）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！