enter image description here

In this ER diagram, can anyone explain how we should convert the many to many relationships ('view', 'update' and 'diagnose') to Relational Schema and then SQL? What I think is that there shouldn't be any relational table for 'view', 'update' and 'diagnose' for all many to many relationships. Instead, what I think is that we just grant permission to the specific users on whether they can 'view', 'update' or 'diagnose'. Can anyone make it clear or show me how to do it?

  • I think you are confusing permissions and relationships, they are not the same. – mustaccio Apr 11 '19 at 20:16

Let's start from a point that your ER diagram should not include possible user roles or actions such as view or update. You have to clearly specify your entities(objects for which you want to create separate tables) and connections between them which are not actions like you specified in your example, instead they show the relations between entities.

After creating your ER diagram you can change many-to-many relationships into relational schema by creating 3 corresponding tables; 2 tables for entities themselves and one which connects them by showing their relation, like for example User, Role and UserRole to specify the role for the users. After that you can give permissions to users from your application or from your dbms if they are database users, to filter and specify who can see what.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.