2

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

  • "All data created by an Employee" -- New tables? New rows? Update columns? What? Are you talking about an Audit trail? Security issues? What? – Rick James Dec 23 '17 at 18:35
  • 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. – j10 Dec 25 '17 at 5:54
  • Do different employees have different mysql logins? Or is there an API between the employees and the databases? (The latter is preferable when tackling auditing/security/etc issues.) – Rick James Dec 25 '17 at 16:51
  • Sorry Rick I did not understand the difference between the two. We store the credentials in Employee table - each employee having their own credentials and they are verified by an API. It's a cloud based application. – j10 Dec 26 '17 at 7:13
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.