0

I believe my problem is something similar to this but I am not really sure. I am not even sure my title is on topic. Using mysql. I have the following ACL design for a website:

### tools
id
name

### tasks
id
tool_id references tools.id
name

### roles
id
tool_id references tools.id
name

### access
id
role_id references roles.id
task_id references tasks.id
has_access

Website has several tools, each tool can do several tasks. Each tool has several roles (ex: admin, mod, user) and each role is allowed to perform specific set of tasks based on access table.

Problem: how do I achieve that access.task_id belongs to the correct tool and not some random tool? For example, if we have role "editor" in tool "articles" which has tasks "delete", "edit" and "publish", how do we prevent someone inserting into access table a task id belonging to "execute_shell_script" which is from a totally different tool? So roles.tool_id=tasks.tool_id for each row.

I suspect that my design is flawed in some way so feel free to point me to a better solution.

And a more general question, when db constraints get really complicated is it generally better to do them only programatically?

Community
  • 1
cen
  • 93
  • 1
  • 7

1 Answers1

0

What you have described is modeled as follows: enter image description here

What you are attempting to do is a design flaw (logic bomb); it's called a Fan-Trap in relational database design.

To get around the logic flaw that you are bumping into, you must collect (resolve) the relation from all three tables, Tasks, Roles and Tools into a single associative entity as follows: enter image description here

Each row of Access collects the relationships between Tools, Tasks and roles.

As far as complexities of constraints is concerned, I think you are confusing the implementation method with design. Good design will clarify the intent, help the developers and maintain performance. The constraints are declarative (and in the SQL standard) to keep things simple while offering verification, validation and enforcement of the design. When you get to the point where you are considering implementing your constraints in code; take a moment to reflect (can an alternate design address this issue in a more elegant way? That's the fun part of design anyway).

Sometimes it cannot (Business Rules come to mind), and the use of triggers/functions is supportable; but your job just got a LOT more complex and bug prone.

Guy Birkbeck
  • 86
  • 1