It is possible to database available that provides you the advantage of referential integrity or being able to make use of a SQL type language for querying, but additionally allows organizations be loosely defined regarding their data characteristics as well as the associations together?

E.g. have a RBAC type model in which you have Permissions, Customers, User Groups &lifier Roles. An intricateOrversatile model might have the next rules:

  • Roles might have a number of permissions along with a permission can fit in with a number of Roles
  • Customers might have a number of permissions along with a permission can fit in with a number of Customers
  • Customers Groups might have a number of permissions along with a permission can fit in with a number of Customers Groups
  • Customers might have a number of roles along with a role can fit in with a number of Customers
  • User Groups might have a number of roles along with a role can fit in with a number of User Groups
  • Roles might have a number of roles along with a role can fit in with a number of Roles

To model the above mentioned within an RDBMS would involve the development of plenty of intersection tables. Ideally, all Let me define within the database may be the organizations themselves (User, Role, etc) and several mandatory characteristics. Anything else would then be dynamic (i.e. no DDL needed), e.g. I possibly could produce a User with a brand new attribute which wasn't pre-defined. I possibly could also produce a relationship between organizations that has not been predefined, although the database would handle referential integrity just like a normal RDBMS.

The above mentioned could be accomplished to some extent inside a RDBMS by developing a table to keep organizations and the other someone to store associations etc, but this excessively reduces the SQL required to perform simple queries and could also provide performance implications.

Most NoSQL databases are made to scale perfectly. This is accomplished at the expense of consistency, which referential integrity is a component of. So most NoSQL don't support any kind of relational constraints.

There's one sort of NoSQL database that does support relations. Actually, it's designed specifically for relations: the graph database. Graph databases store nodes and explicit relations (edges) between these nodes. Both nodes and edges can contain data as key/value pairs, without having to be associated with a predefined schema.

Graph databases are enhanced for relational queries and great graph procedures, for example locating the least path between two nodes, or finding all nodes inside a given distance in the current node. You would not need this inside a role/permission scenario, but when you need to do, it will be a great deal harder to attain utilizing an RDBMS.

An alternative choice would be to build your entire data layer a hybrid, using a RDBMS to keep the relations along with a document database to keep the particular data. This could complicate the application slightly, however i don't believe it's this type of bad solution. You will be using two different technologies, both coping with the issues these were designed to cope with.