Maintain link tables with listbox in the UI

Question

5.00/5 (1 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 0:55am

louisebexter

Add a Solution

Comments

Herman<T>.Instance 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)

2 solutions

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.

Posted 15-Nov-12 12:34pm

louisebexter

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Greysontyrus · Accepted Answer · 2012-11-14T23:56:00

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.:

Red=1,Yellow=2,Blue=4
7&2=2; //7 has yellow
5&2=0. //5 does not