This is something that I've always struggled with in terms of best practise.
(data simplified for the purposes of this)
So I have listbox A which contains a list of colours, red, blue, yellow etc.
User can choose one or more of these, so Red and Yellow and save to the database, which saves UserID, ColourID x 2 records. So Red (colour ID 1), UserID 1, Yellow (colour ID 2), UserID 1.
Sound Ok so far?
So the user goes back into the form later on. On the load of the page I bind the listbox again with the colours, and by reading that link table joined to User 1, I can see that Red and Yellow need to be selected, so I can do that. Again, pretty straight forward.
But what if the user decides to DESELECT Yellow and SELECT Blue. I would have to find the selected items of the listbox, So this time I'd have Blue (colour ID 3), UserID 1 and Yellow again (colour id 2), User ID 1.
Technically I dont need to do anything with one of the records, that hasnt changed, but do I delete the link record to the Red colour, and insert one linking the user to the Blue colour (seems a waste of IDs to keep deleting and inserting records)? Or do I change the colour ID in the link record to 3.. What if they just decide to Keep yellow only? There are many options here. I'm just struggling with the best way/logic to persist the values chosen in the listbox, with the database without constantly deleting and inserting rows. Maybe someone has a cool way of dealing with it or some kind of method I havent thought of.
Single drop downs are not an issue since you are only dealing with one record here.
I'm using Linq to SQL by the way, and C# 3.5.