Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data

Question

We are building a system with concept of Admin and Employee. so basically Admin is an employee with all powers and can view all the data created by other Employee.

CREATE TABLE `Vendor` (
  `vendor_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email_Id` varchar(40) DEFAULT NULL,
  `landline_Number` varchar(15) DEFAULT NULL,
  `mobile_Number` varchar(15) DEFAULT NULL,
  `address_Line1` varchar(65) NOT NULL,
  `address_Line2` varchar(65) DEFAULT NULL,
  `city` varchar(255) NOT NULL,
  `pincode` int(6) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`vendor_Id`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `username` varchar(40) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_username_unique` (`username`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1


CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `action_emp_id_foreign` (`emp_Id`),
  CONSTRAINT `action_emp_id_foreign` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

There are other tables Roles and EmployeeRoles which I thought were not required here.

Approach 1 : Now, when the Admin logs in to see all the Actions created by everyone

1. we first need to query the Employee table to find all the employees of that Vendor (we will have the Vendor_Id stored in the session when the admin/employee logs in)
2. Then query the Action table with where in employee_Id array from Step 1

Is this a good approach ?

Approach 2 : or in Action table, I shall store Vendor_Id for each record (mainly all this effort only so that when Admin logs in I can easily retrieve all the records for that Vendor. When Admin logs in from the session I can easily find the Vendor_Id and query the Action table.

I don't know at this moment which would be a better approach. Any suggestions ? Like Action, there are other 3 tables where similar concept needs to be applied.

Edit 1: There can be a case where we can have multiple vendors registered under a Single brand (future extension) and the Super-Admin would like to analyze the data across multiple branches.

Edit 2:
Employees can create ** new rows ** by using the features; but admin would be interested to see all the activities that are going on in that feature by different employees. Basically, admin should be able to see how are different employees using the system

Answer 1

Maybe have separate table for Vendor_id, Admin_id, employee_id. You could join on isnull(admin_id, employee_id).

That way, if the user has an admin_id, they could see all the records. If they don't, they would see only the records for their employee_id.

Answer 2

This is about the approach 2. It depends upon your requirements. How important are these queries on the Action table? Are these run often and need to be fast? Example queries can be:

• List all actions for an employee
• List all actions for an employee on certain dates or a range of dates (in a particular year or month, for example). Note: this will require a timestamp column in the Action table.
• List all actions for an employee for a specific vendor, etc.

Further, appropriate indexes can be created on the action table columns for the queries to access the data efficiently.