Hi Team,
I am designing data model in DB which is Normalized DB.
The high-level functionality is there are users who have certain roles. One user can may have more than role.
Role can perform certain Operations.
Each operation may have certain priviliges ( CRUD) depending on role he is.
Example- A Admin role
I designed four master tables and 2 bridge tables.
- User Master table user_id as PK.
- Role Master table role_id as PK.
- Operation Master table Op_Id as PK.
- Privilige Matser table as Priv_Id as PK.
Bridge tables
1.User_to_role - Bridge table having colums ( usrroleid (PK), user_id, role_id).
2.Operation_to_privilge. Bridge table.
colums ( op_priv_id(PK), op_id, priv_id, userroleid(FK)
My team is saying they dont want to have two Bridge tables instead create One with all four major columns.
userid, roleid, opid, prvid.
so that they would see which user on what role for which Operation what privileg he has.
I felt is more like denormalized table.
What are procs and cons here when they have these colums as audit colums in txn table ?
Thanks in advance for ur inputs.