All, I work at a university and having discussions with our third party ERP vendor, we found out that the vendor's table for biographical information stores all the data from different constituents. The same table stores information about candidates, students, faculty, staff, alumni, etc.
This presents a challenge to us since we will have to create views to achieve row-level security for our end users and delegate access to the department based on their need. For instance, employees in the admissions office should not be able to query the biographical information of other employees, etc
Even in the vendor's application, users in the admissions office are able to access other employee's biographical information since the software runs queries on the same table. That seems to be a huge security issues in my view. The software has been created more than 10 years ago and little progress has been made towards fixing that.
As a general rule of thumb, what are the best practices for designing a biographical table? Should there be different tables for each constituent(candidate, employee, faculty) or one table for all of them?