Database design for user permissions?

Question

MySQL

Part of the output of `php artisan migrate --pretend`

InteractiveForms: create table `application_models` (`id` bigint unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `name` varchar(255) not null, `title` varchar(256) not null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_models` add unique `application_models_name_unique`(`name`)
InteractiveForms: create table `application_model_instances` (`id` bigint unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `model_id` bigint unsigned not null, `creator_id` bigint unsigned null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_model_instances` add constraint `fk_5e4adfe3e91ac5916249954803b78d92_model_id` foreign key (`model_id`) references `application_models` (`id`)
InteractiveForms: alter table `application_model_instances` add constraint `fk_5e4adfe3e91ac5916249954803b78d92_creator_id` foreign key (`creator_id`) references `application_users` (`id`)
InteractiveForms: alter table `application_model_instances` add index `application_model_instances_model_id_index`(`model_id`)
InteractiveForms: create table `application_model_fields` (`id` bigint unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `model_id` bigint unsigned not null, `title` varchar(256) not null, `name` varchar(256) not null, `type` enum('field_text', 'field_textarea', 'field_checkbox', 'field_radio', 'field_select_inline', 'field_select_list', 'field_select_dropdown', 'field_datetime', 'field_date', 'field_email', 'field_phone', 'field_url', 'field_image', 'field_file', 'field_coordinate', 'field_user') not null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_model_fields` add constraint `fk_2d216afb8aca7f83e97be47b6cb3ddf1_model_id` foreign key (`model_id`) references `application_models` (`id`)
InteractiveForms: alter table `application_model_fields` add unique `application_model_fields_name_model_id_unique`(`name`, `model_id`)
InteractiveForms: create table `application_roles` (`id` bigint unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `name` varchar(255) not null, `title` varchar(256) not null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_roles` add unique `application_roles_name_unique`(`name`)
InteractiveForms: create table `application_role_model_permissions` (`id` bigint unsigned not null auto_increment primary key, `model_id` bigint unsigned not null, `role_id` bigint unsigned not null, `can_create` tinyint(1) not null default '0', `can_all` enum('can_see', 'can_edit') null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_role_model_permissions` add constraint `fk_6733a186b1563cb971f668fa04a37a95_model_id` foreign key (`model_id`) references `application_models` (`id`)
InteractiveForms: alter table `application_role_model_permissions` add constraint `fk_6733a186b1563cb971f668fa04a37a95_role_id` foreign key (`role_id`) references `application_roles` (`id`)
InteractiveForms: alter table `application_role_model_permissions` add index `application_role_model_permissions_model_id_role_id_index`(`model_id`, `role_id`)
InteractiveForms: create table `application_role_model_permissions_by_user_fields` (`id` bigint unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `role_id` bigint unsigned not null, `model_field_id` bigint unsigned not null, `can_this` enum('can_see', 'can_edit') null, `can_dependent` enum('can_see', 'can_edit') null) default character set utf8 collate utf8_unicode_ci
InteractiveForms: alter table `application_role_model_permissions_by_user_fields` add constraint `fk_4b244bfa536a4df63d1d58ba0cc27997_role_id` foreign key (`role_id`) references `application_roles` (`id`)
InteractiveForms: alter table `application_role_model_permissions_by_user_fields` add constraint `fk_4b244bfa536a4df63d1d58ba0cc27997_model_field_id` foreign key (`model_field_id`) references `application_model_fields` (`id`)
InteractiveForms: alter table `application_role_model_permissions_by_user_fields` add index `role_id`(`model_field_id`)
InteractiveForms: create table `application_role_users` (`id` bigint unsigned not null auto_increment primary key, `user_id` bigint unsigned not null, `role_id` bigint unsigned not null) default character set utf8 collate utf8_unicode_ci

What is:
application_models - types of models (as separate tables in the database)
application_model_instances - models themselves,
application_model_fields - fields of models
application_role_model_permissions - global permissions on models depending on the role (for example, the 'editor' role can edit all models with types: 'news' and 'event')
application_role_model_permissions_by_user_fields is the trickiest part, application_model_fields can have multiple field types (text, files, multi-select, etc.) and also a reference to the user. application_role_model_permissions_by_user_fields contains information that if the user is mentioned in some field of the model, then he has such and such rights.
For example, the 'task' model contains two fields of type user: 'responsible' and 'who can view'
If the user is mentioned in the responsible, then he can edit the task, if he is mentioned in 'who can watch', he can only view the task, otherwise he doesn't have access.
The problem is that application_role_model_permissions_by_user_fields contains a model_field_id reference to application_model_fields and this reference is considered valid only if the model_field_id refers to a field with type 'user', otherwise this data does not make any sense.
Maybe there is a better approach?

Answer 1

Oh my God..... You would at least read the documentation before doing THIS.
First, there is a bouncer package, look it up. Allows you to issue permissions to any entity or separately, a flexible option.
Secondly, I am 99.99% sure that there is already a completely ready-made version of dynamic models on the Internet, and it will not be difficult to attach permissions to them. What is the point of writing bicycles?
Thirdly, making three tables with the same purpose is stupid.