Inside database application we know physically, that only app_staffs can access app_warehouse. But when we try to merge users table and app_users table, there will be anomalies, because there are 2 roles.
- Is RBAC need to be standalone outside application database? Can it have relationship with application table?
- How to combine my current (simplified) RBAC database with my current (simplifed) application database and avoid any anomalies?
- What is your best practices to avoid anomaly like this?
- Or there is something I missing?
If cannot RBAC database cannot be merged with application database, what should I do to maintain data consistency of the user data, is that really need logical constraint (like trigger, check constraint, etc)?