Click here to Skip to main content
13,290,261 members (53,463 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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.

Thanks, Louise.
Posted 14-Nov-12 1:55am
digimanus 14-Nov-12 9:18am
how many colours you actually define in th system?
louisebexter 15-Nov-12 18:36pm
there will be about 20 options that could possibly be linked, so to use colours, 20 colour options per user (many to many)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Best practice dictates that you delete any link table (many to many table) records for colours that are deselected and insert for new ones. Are IDs really going to be an issue in your db? Are that many changes made? If so, consider using a Guid for the unique key of the UserColour link table. Alternatively you could use the User Id and Colour Id combo as the unique key, but I have never liked that.

In many cases I delete everything first and re-insert the new links. This can be useful but isn't ever desirable.

The issue with re-using the ID is that it completely undermines the point of having a unique ID, even on a link table. You may with to include other details pertaining to that link in the future such as CreatedBy, Created date or whatever.

If the number of colours you are using are set then you could use a single bitwise value. I can explain that here in length but I imagine that it is not the case that you want to, or are able to implement this data structure. If you want to look it up then there are plenty of resources out there and both .Net and SQL can use bitwise operands

Hope that helps :D

bitwise I.E.:

7&2=2; //7 has yellow
5&2=0. //5 does not
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

you are right its not really going to be a problem in terms of user traffic, to delete and reinsert link records. They probably wont change then THAT often either..Just seems a bit clunky, but probably the easiest solution for this app. thank you for your response though.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171207.1 | Last Updated 15 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100