What is the best practice to make edit in junction tables?
Items{ItemId, Name, Price...}
Shops{ShopId, Name, Address...}
ItemsInShops{ItemId, ShopId, DeliveryDate...}
Now I have 30 items in one shop. I want to edit that list and I uncheck 10 items and check 50 new items.
I do this in the following way: Remove all rows from 'ItemsInShops' by 'ItemId' and add new values. I don't think that this is good solution. Is there any better way to do this kind of update?
Maybe I didn't express problem with good example. Take a look at this:
User{UserId, Username, Password...}
Roles{RoleId, Name, Description} // Admin, Member, Superuser, Junior etc
UsersInRoles{UserId,RoleId}
User can have any number of roles.
John > Admin, Member, Superuser
That is three rows in junction table 'UserInRoles'.
If I want to update this user to have the following roles:
John > Member, Junior
Now I do this update on database in the following way:
I remove all John roles from 'UserInRoles' table and add new data. I don't know is there any better way to do this update, other than delete all and insert new? What if update fails from some reason (lost internet connection for example)?
I don't know is there any better way
to do this update, other than delete
all and insert new?
You don't have to delete all the rows to start with.
You can delete only the rows that no longer apply, and insert only the rows that are new. Or you can update a value that no longer applies with a value that does apply.
So to get from this
Name Role
--
John Admin
John Member
John Superuser
to this
Name Role
--
John Member
John Junior
You can delete what no longer applies . . .
delete from userinroles
where Name = 'John'
and (Role = 'Admin' or Role = 'Superuser');
and insert what does apply.
insert into userinroles (Name, Role)
values ('John', 'Junior');
Or you can update a value with a new value.
delete from userinroles
where Name = 'John'
and Role = 'Admin';
Followed by
update userinroles
set Role = 'Junior'
where 'Name' = 'John' and Role = 'Superuser';
You said
What if update fails from some reason
(lost internet connection for
example)?
That's what transactions are for. Multiple statements within a single SQL transaction are all or nothing--either they all succeed, or no changes are made.
Based on the comments, there is no better way to add/remove the database data, than to add/remove the database data.
Alternatively, you could have columns in the database for status information, e.g. "paid" "sold" "date delivered" "cancelled" etc. Then instead of deleting records, you would update the status. That makes it easier to check past transactions and keep records correct (e.g. "uncancel").
You might look in BOOKS Online at the MERGE keyword.
Your updated question is interesting.
efficiency wise, removing then adding the data could be done with MERGE.
Regarding the two queries running in a row, when they really form one process, you need transactions. They are "all or nothing" settings so that a set of queries either all succeed, or all fail (are all "rolled back"). Transactions are very useful, e.g. "user pays + product is delivered" or "past data is archived + deleted", and they are discussed in detail in many textbooks and websites.
