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?