Multiple User Permissions for other Entities

Our company is having a problem migrating from a single use开发者_StackOverflow社区r application to multiple. Our setup is that a User would own a Product, and that ownership is through a foreign key. Since they owned the Product they would have permission to perform all functions on the Product. If you don't own the Product and weren't an Admin, then you could not access the Product or any of its functions.

Now we are trying to open the access to the Products up to other users who have been Grouped together with the respective owner, to collaborate. These other users including the owner may have limited functionality based on a set of permissions. The scope of the permissions have been designed to be based on

1. the Person to Product ownership relationship (FK)
2. the Person to Product relationship via a Person_Product bridge table
3. the Person to Group to Product relationship where a Product belongs to one and only one Group
4. the Person to Organisation (Applicantion Wide Permissions) which is the users default permissions if the permissions via the other relationships are not declared.

These relationships have already been implemented but problems have come up where the system is doing alot of calculations based on Relationships. e.g. Getting the User's permissions for a collection of Products has significantly hindered performance. Also the flexibility of our system is being limited by new relationships and abstractions.

Database wise, I see relationship 2 as being the fundamental block that could be used to express the behaviour of all the relationships and any future abstractions our company would be asked to layer on top.

Am I correct in this line of thinking? Or are we on the right track now? Any helpful input would be greatly appreciated.

The relevent technologies are tagged.

a strictly normalized relational database can be a bottleneck when it comes to applications like this. i haven't seen THE solution yet, but you can speed up things if you consider tables that would be forbidden in a normalized DB, as some sort of cache.

my approach is to have a strictly normalized DB-part containing the actual data and a non normalized part that acts as a cache ... updates between those 2 parts are one way: from the normalized part to the cache (in case of inconsistent data / anomalies in the cahce, the cache can be rebuild)

the advantage is: your permissions per user can be aggregated (maybe even as a binary object) and be fetched as a single row per user/per product/etc, as long as the permissions come from the cache ... depending on the use-case this can be very fast

the downside is: updates to cached data are more complex: you have to update the normal DB, and then you have to update the cache, or your update will not be visible to the cache until it's rebuld ... this is no big problem if your permissions aren't updated every 2 seconds

plus: you have to implement and maintain the cache and the applications that make use of this ...

so one warning: even if this can (== it's not guaranteed to be in every case) be very fast, you should not use this approach as long as there are any other options left. (you should avoid breaking the normalization as long as you can; is the DB properly indexed? could such a cache be setup somewhere else, ie. a service application somewhere?)