I have implemented RBAC (Role Based Access Control) tables. I have a requirement to add extra attribute on some roles, like 2nd_pwd
attribute in Admin
role, address
attribute in Customer
role.
I have done this design, but it violates data integrity. When you removed (let's say) Admin
role, you also need to remove admin data from Admin
table. So I decided to add trigger for this job.
Question:
- Is there a design for this requirement without trigger but still doesn't violate data integrity?
- What is the common implementation for this requirement? Is it normalized enough?