What would be the correct way to normalize a scenario where we have Users and Roles where certain roles have the same attributes and other roles have different attributes? Let's take the following example:
tb_User(user_id, first_name, last_name, e_mail);
tb_Role(role_id, role)
tb_Plant(plant_id, plant)
tb_Entity(entity_id, entity)
I have roles like: Admin, General Manager and Plant Manager and Entity Manager. Admin and General Manager roles don't have any other attributes, but plant manager has a plant associated with it and Entity Manager has an entity associated with it. Should I create one table like this and have Plant/Entity have a value where it applies like this:
tb_user_role(user_role_id, user_id, role_id, plant_id, entity_id)
or have separate tables for plant and entity like this:
tb_user_role(user_role_id, user_id, role_id)
tb_user_role_entity(user_role_entity_id, user_role_id, entity_id)
tb_user_role_plant(user_role_plant_id, user_role_id, plant_id)
Another consideration is that one one user can have many plants, so one plant manager role and many plants, same thing for entity.
I appreciate your help,
