Updating selected options Logic

[royd16]

Hi!,
can some help me with a problem?

Here are my tables: User, Profiles, and one for "Profiles per user"

In the last one, I save the ID of the user and the ID of the profiles for that user. This profiles are selected through checkboxes. My question is, What's the best way to update the records on the database?,
because there's three scenarios, they selected a new profile (INSERT), they leave like it was (NOTHING), or they unselected a profile (DELETE).

But I can't figure out a "nice" way to do this. Should I just erase all the records from the table, and then insert all the new ones even if there are unchaged records?,
Any ideas?

[ck9663]

This would mean there can be multiple profiles per user.

Why don't you just add a ProfileStatus field that you can just change to 0 for Inactive simulating a DELETE and 1 for active simulating an INSERT or a reactivation of a profile.

For me, this would take 2 steps. INSERT those that are not yet on the profile and activate those SELECTED (if the record is currently selected it will overwrite the same value - which is 1).

-- CK

[Brad Orders]

Assuming that you want a user to have many profiles, there is more than one way to do this.

One:
The user presses "Save", and their information is deleted from the database, and the new information saved. This is the option you have mentioned.

Two:
The user presses "Save". The code (eg .NET) checks for profiles that have been marked as "Delete", and deletes them. The code checks for profiles that have been added, and adds them. The code then updates the rest.

Three:
There is a "Save" button on each Profile line on the screen, and the user saves after each change. This will make it easier for you to manage, but I guess it depends how many changes the user makes at one time. They might get sick of pressing the "Save" button after a while.

There is no right answer here. They are all valid choices. Personally, I would either go for option one and make sure I wrapped it in a transaction in case something went wrong half way through, or go for option two.

Hope this helps

[royd16]

Ok, Thanks! I just want to use the option that gives me the best performance gain.
seeing this options I think I'll try option number two.

Thanks again