4

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:

enter image description here

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?

  • 1
    See this question and answer: Best data modelling approach to handle redundant foreign keys in relational model and this one: Many to Many and Weak Entities. By the way, the design is not circular. You haven't provided with the "arrows" of the relationships (which tables reference which) so we can't be sure but seems like it is not. – ypercubeᵀᴹ Jun 13 '16 at 7:10
  • @ypercubeᵀᴹ Aren't 4 tables tied-up with relations enough to be called a circular relationship? Sorry about the diagram, I was on a bit of a hurry. I updated the image. Now that I look at it, it seems the direction all points to the account. Does this not make it a circular relation, so that it is a valid design? – Vic Jun 14 '16 at 0:12
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.