0

Let's say there is a a system and you have three entities:

  • User
  • Team (group of users)
  • Role

And you want to setup up some permission relationships, with the flexibility in the database anyway, to say:

  • User X can change permissions of User Y
  • User X can change permissions of Team Y
  • User X can change permissions of Role Y
  • Team X can change permissions of Role Y
  • Team X can change permissions of Team Y
  • Role X can change permissions of Team Y
  • Role X can change permissions of Role Y

With a similar scenario, I currently have a table with the following schema:

  • SubjectType (User | Team | Role)
  • SubjectId (integer - not foreign key)
  • TargetType (User | Team | Role)
  • TargetId (integer - not foreign key)

This is allows all the relationships to be specified in one place, however, as there are no specified relationships, it has the problem:

  • Role A is configured with access to teams with team IDs X,Y,Z
  • Teams Y and Z are removed with no errors elsewhere
  • When querying targets for Role A, the team IDs Y and Z are still returned.

As it stands, I can see two options, each with compromise involved (pros = +, cons = -):

1. Leave as is with data in one place but remove orphaned permission relationships when the entity is removed.

  • (+) Keeps one table and single query simplicity and manually remove team permission entries when a team is removed
  • (-) Will require 3 manual deletion operations (User | Team | Role). Whilst there would be a single deletion function where this could take place in a repeatable manner, there is no hard and fast guarantree

2. Specify each relationship in its own table.

  • (+) ACID guarantee will prevent entries becoming orphaned
  • (-) Much greater table verbosity, 7-9 tables for basically the same thing
  • (-) More queries, more joins (specially if using class table inheritance)

I'm kind of leaning towards the second option with the thought of adding the extra complexity below and abstracting it into single access functions and am thinking that is worth the extra work (involves a bit of a rewrite).

However, I am wondering if there is a different option I have missed, or if there are some strong experienced based recommendations on this problem?

Using SQL Server 2012 with this setup but I imagine this to be a generic SQL / database issue.

mgibson
  • 6,103
  • 4
  • 34
  • 49
  • you can use cascade delete on foreign relation. So when master table is deleted, the child also deleted. – Adinugraha Tawaqal Sep 04 '18 at 09:34
  • https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php – Adinugraha Tawaqal Sep 04 '18 at 09:34
  • Unfortunately, subjectId and targetId are just integers not foreign keys in order to generically store (User | Team | Role) – mgibson Sep 04 '18 at 09:38
  • " strong experienced based recommendations on this problem?" -- You're asking for opinions? – nicomp Sep 04 '18 at 09:41
  • 1
    I am going to suggest you use second option of table with relations that can take advantage the foreign key relation. – Adinugraha Tawaqal Sep 04 '18 at 09:44
  • if you put all data in one table you gonna have problem with indexing with a lot of column – Adinugraha Tawaqal Sep 04 '18 at 09:46
  • `7-9 tables` and then new entity or kind of relations shows up... – Ivan Starostin Sep 04 '18 at 10:26
  • @IvanStarostin Exactly, it's another table. However, each of the items referenced in the single table at the moment do exist in their own tables, so database changes are required anyway – mgibson Sep 04 '18 at 11:50
  • @mgibson I'm hinting to you about multiplication, not about adding. – Ivan Starostin Sep 04 '18 at 12:04
  • @IvanStarostin Ah of course. If there were the possibility of any more entities coming in to the picture, I think I'd favour the first option. Thankfully, in this case, I am pretty certain they are all that will be required – mgibson Sep 04 '18 at 12:11
  • From this point it becomes totally opinion-based (and experience-giving) solution. Also you could have `user_id, team_id, role_id` nullable columns in 1st case table to have declarative FKs. One may decide to get rid of FKs because of FK overheads (and deadlocks) someday... Toss a coin ) Gain pros and cons from a real exploitation, perform refactoring, repeat. – Ivan Starostin Sep 04 '18 at 12:25

0 Answers0