What is the best way to manage a junction table? (delete and insert)

Question

I have two tables Users and Permissions, and a junction table UserPermissions like this (these tables are examples) :

CREATE TABLE Users (
    UserLogin varchar(50) PRIMARY KEY,
    UserPassword varchar(50) NOT NULL,
    UserName varchar(50) NOT NULL
);

CREATE TABLE Permissions (
    PermissionKey varchar(50) PRIMARY KEY,
    PermissionDescription varchar(500) NOT NULL
);

-- This is the junction table.
CREATE TABLE UserPermissions (
    UserLogin varchar(50) REFERENCES Users (UserLogin),
    PermissionKey varchar(50) REFERENCES Permissions (PermissionKey),
    PRIMARY KEY (UserLogin, PermissionKey)
);

I created a form to add and remove user permissions but I do not know what is the best way to manage the UserPermissions table

Imagine that the user SomeUser has 3 permissions:

INSERT INTO UserPermissions (UserLogin, PermissionKey)
VALUES ('SomeUser', 'TheKey') , ('SomeUser', 'TheKey1') , ('SomeUser', 'TheKey2');

But that later (with the form) I want to change these permissions and put only: TheKey1, TheKey4, TheKey5 (so there are TheKey and TheKey2 permissions that have been removed)

By what means can I make this change effectively, I found this solution that works:

DELETE FROM UserPermissions WHERE UserLogin = 'SomeUser';
INSERT INTO UserPermissions VALUES ('SomeUser', 'TheKey1') , ('SomeUser', 'TheKey4') , ('SomeUser', 'TheKey5');

Is there a better way to modify five, ten or twenty Permissions at the same time?

score 0 · Answer 1 · answered Jun 11 '19 at 09:58

I think that deleting all permission and then inserting them all again, like you do, is a good approach.

If you don't want to do that you have to know what the permissions were and only update the changes. This is more complex, and the change for errors is greater.

What is the best way to manage a junction table? (delete and insert)

1 Answers1