How can I avoid this circular relation?

Question

I based my account/user table design following this article. So I have set my table relationships to look something like this:

User <- Memberhip -> Group -> Account

With this design, a user is tied to group via a membership table. A group belongs to an account. so far so good.

Now I want to add a new resource catalog that belongs to an account, but can be accessed by a group. A group may have different access levels to different catalogs; it may have full edit access to one catalog, but read-only to a different one. A catalog may be accessed to by different groups. Since this is a many-to-many relationship, a pivot table is required which will also determine the groups access level to each catalog:

I tried putting the catalog table directly under a group, but with this approach, the account is not the direct owner of the catalog but the group is. its harder to give access to different groups. I could also create new groups for every new catalog, but then existing groups can belong to only one catalog.

How can I fix this design so that is no circular relation in it?

Answer 1

In my opinion, you can't remove the relationships you have modeled.

The general reason for avoiding such "circular" models is that in 2 years, when the next person maintains this database, they will think that they can go from group to catalogue via account or role - and technically, they can, but the route they choose determines the meaning of the relationship.

If they go via account, it is saying "This group's account owns the following catalogues".

If they go via role, it is saying "This group is permitted to access these catalogues, with these role permissions".

Both are valid relationships.

In my opinion, set really good column names that describe the relationships. If your DBMS supports adding comments/documentation on DB objects, do so (even though this feature seems little used in practice) - it is very hard to lose the documentation when it is part of the object definition (as opposed to some wiki page or text document).

Consider adding views called 'GroupCatalogues' and 'AccountCatalogues', which give a fairly good indication of what they will show, and encapsulate the joining rules in those views so that correct data is shown if your developers access the views rather than the tables directly.

Consider whether you can revoke direct access to the tables to enforce developers access the views.